Find All NOT NULL Columns Without a Default Constraint

Rarely, I'll run into a situation where it would be useful to know which columns that are specified as NOT NULL don't have a default constraint as well. For example, I've seen a table with two dozen columns, all but a few set as NOT NULL. This in itself isn't an issue, but on this occasion, for debugging purposes, we were constantly INSERTing a test row of data into the table. Having to specify a default value for each column got to be annoying after a while.

There are, of course, other ways to skin this cat, We could have created a SSMS template and used the old "Ctrl-Shift-M" key combo to type in just the values we needed. Or we could have created a script, or even a stored procedure to handle the default values for us. For various reasons, those solutions wouldn't work in our environment, so I decided to attack the problem at its root.

The code below looks at all non-empty tables and prints out T-SQL code to add the default constraint to each column it finds. Note that I've made an attempt to select a reasonable default value for all the common data types, but (a) the list is not complete, and (b) your idea of a "reasonable" default value may be very different from mine. So don't just copy and paste the output from this script and hit F5 - take a moment to read each line and be sure it does what you want; if not, you can easily change it!

-------------------------------------------------------------------------------
-- For all tables in the current database that have rows, finds all "NOT NULL" 
-- columns that don't have a DEFAULT constraint, and emits the ALTER TABLE     
-- statements needed to add the DEFAULT constraints for them.                  
-------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @SchemaName      sysname         SET @SchemaName     = ''
DECLARE @TableName       sysname         SET @TableName      = ''
DECLARE @ColumnName      sysname         SET @ColumnName     = ''
DECLARE @ColumnType      sysname         SET @ColumnType     = ''
DECLARE @ConstraintName  sysname         SET @ConstraintName = ''
DECLARE @Sql             NVARCHAR(MAX)   SET @Sql            = ''

DECLARE cur CURSOR FOR
    SELECT SCHEMA_NAME(t.schema_id)    AS 'Schema Name'
         , OBJECT_NAME(c.object_id)    AS 'Table Name'
         , c.name                      AS 'Column Name'
         , ty.name                     AS 'Column Type'
      FROM sys.columns   c
      JOIN sys.tables    t
        ON c.object_id = t.object_id
      JOIN sys.types     ty
        ON c.user_type_id = ty.user_type_id
     WHERE c.is_nullable                  = 0
       AND c.is_identity                  = 0
       AND c.is_computed                  = 0
       AND t.type_desc                    = 'USER_TABLE'
       AND t.is_ms_shipped                = 0
       AND ISNULL(c.default_object_id, 0) = 0
       AND 0 < (SELECT SUM(row_count)
                  FROM sys.dm_db_partition_stats
                 WHERE object_id = OBJECT_ID(t.name)   
                   AND (index_id = 0 OR index_id = 1))
  ORDER BY 'Schema Name'
         , 'Table Name'
         , 'Column Name'

OPEN cur

FETCH NEXT FROM cur
 INTO @SchemaName, @TableName, @ColumnName, @ColumnType
        
IF @@FETCH_STATUS = 0
BEGIN        
    PRINT 'USE ' + DB_NAME(0)
    PRINT ' ' 
END

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ConstraintName = QUOTENAME('DF_'+ @TableName + '_' + @ColumnName)
    
    SET @Sql = ''
             + 'PRINT ''Processing: ' + QUOTENAME(@SchemaName) + '.' + @ConstraintName + ''''
             + ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) 
             + ' ADD CONSTRAINT ' + @ConstraintName 
             + ' DEFAULT ('
             +
                CASE @ColumnType
                    WHEN 'char'             THEN ''''''
                    WHEN 'nchar'            THEN ''''''
                    WHEN 'varchar'          THEN ''''''
                    WHEN 'nvarchar'         THEN ''''''
                    WHEN 'sysname'          THEN ''''''
                    WHEN 'xml'              THEN ''''''
                    WHEN 'udtUserID'        THEN ''''''
                    WHEN 'text'             THEN ''
                    WHEN 'ntext'            THEN ''

                    WHEN 'bigint'           THEN '0'
                    WHEN 'int'              THEN '0'
                    WHEN 'smallint'         THEN '0'
                    WHEN 'tinyint'          THEN '0'
                    WHEN 'bit'              THEN '0'

                    WHEN 'real'             THEN '0.0'
                    WHEN 'money'            THEN '0.0'
                    WHEN 'smallmoney'       THEN '0.0'
                    WHEN 'float'            THEN '0.0'
                    WHEN 'decimal'          THEN '0.0'
                    WHEN 'numeric'          THEN '0.0'

                    WHEN 'image'            THEN '0x0'
                    WHEN 'binary'           THEN '0x0'
                    WHEN 'varbinary'        THEN '0x0'
                    
                    WHEN 'uniqueidentifier' THEN '0x0'
                    WHEN 'sql_variant'      THEN '0'
                    WHEN 'hierarchyid'      THEN '''/'''
                    WHEN 'geometry'         THEN '0'
                    WHEN 'geography'        THEN '0'

                    WHEN 'datetime'         THEN 'GETDATE()'
                    WHEN 'date'             THEN 'GETDATE()'
                    WHEN 'time'             THEN 'GETDATE()'
                    WHEN 'datetime2'        THEN 'GETDATE()'
                    WHEN 'datetimeoffset'   THEN 'GETDATE()'
                    WHEN 'smalldatetime'    THEN 'GETDATE()'
                    WHEN 'timestamp'        THEN 'GETDATE()'
                    ELSE                         '-1'
                END
                    
             + ')' 
             + ' FOR ' + QUOTENAME(@ColumnName)

    PRINT @Sql    
    
    FETCH NEXT FROM cur
     INTO @SchemaName, @TableName, @ColumnName, @ColumnType
END

CLOSE cur
DEALLOCATE cur

Cleaning User Stuff from the master Database

This is an upgrade to a script I wrote a long time ago. It's handy when you find a system whose master database is somehow full of user objects. (I know that none of us ever make that mistake...)

The biggest improvement is that this script now only generates DROP statement, it doesn't execute them. You have to do that yourself, and you should be sure to back up your master database (you do that on a regular basis anyway, right?), and be darn sure to read all the comments, and of course examine the generated T-SQL code, before executing it. Use at your own risk!

-------------------------------------------------------------------------------
-- Drops all user objects that have been accidentally created in 'master'.     
-- This script erases everything in the master database that is not Microsoft- 
-- shipped.  HOWEVER, be careful: some user objects BELONG in the master       
-- database (database triggers, security objects, for example). Backing up the 
-- master database first would be a prudent step.  Use at your own risk.       
-- NOTE: This script only generates the T-SQL code required.  You have to copy 
-- it and do the actual execution yourself.  You may have to run this script   
-- several times due to dependencies; you may also get error messages about    
-- objects that cannot be found, also due to dependencies.                     
-------------------------------------------------------------------------------

USE master

DECLARE @Sql            NVARCHAR(MAX)       SET @Sql            = ''
DECLARE @ObjectName     sysname             SET @ObjectName     = ''
DECLARE @ConstraintName sysname             SET @ConstraintName = ''
DECLARE @TableName      sysname             SET @TableName      = ''
DECLARE @TypeDesc       NVARCHAR(4000)      SET @TypeDesc       = ''
DECLARE @Type           NVARCHAR(1024)      SET @Type           = ''
DECLARE @Score          INT                 SET @Score          = 0
DECLARE @ObjectID       INT                 SET @ObjectID       = 0

DECLARE @ENumber        INT                 
DECLARE @ESeverity      INT                 
DECLARE @EState         INT                 
DECLARE @EProcedure     NVARCHAR(126)       
DECLARE @ELine          INT                 
DECLARE @EMessageRecv   NVARCHAR(2048)      
DECLARE @EMessageSent   NVARCHAR(440)       

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT
RAISERROR('USE master', 10, 1) WITH NOWAIT


-------------------------------------------------------------------------------
-- Most objects follow this pattern.                                           
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc
      FROM sys.objects
     WHERE is_ms_shipped = 0
     ORDER BY type_desc, name

OPEN cur
FETCH FIRST FROM cur INTO @ObjectName, @TypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF @TypeDesc = 'VIEW'
        BEGIN
            SET @Sql = 'DROP VIEW ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
        ELSE IF @TypeDesc NOT LIKE '%_CONSTRAINT' ESCAPE '$'
        BEGIN
            SET @Type  = REVERSE(@TypeDesc)
            SET @Score = CHARINDEX('_', @Type)
            SET @Type  = SUBSTRING(@Type, 0, @Score)
            SET @Type  = REVERSE(@Type)
            SET @Sql   = 'DROP ' + @Type + ' ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ObjectName, @TypeDesc
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Constraints are the only exception to the pattern above.                    
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc,
           OBJECT_NAME(parent_object_id)
      FROM sys.objects
     WHERE is_ms_shipped = 0
       AND type_desc LIKE '%_CONSTRAINT' ESCAPE '$'
  ORDER BY type_desc, name

OPEN cur    
FETCH FIRST FROM cur INTO @ConstraintName, @TypeDesc, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName)
        RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ConstraintName, @TypeDesc, @TableName
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Done.                                                                       
-------------------------------------------------------------------------------

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT

Reclaim Space using DBCC CLEANTABLE

Image by The Real Estreya via Flickr

I just read Pradeep Adiga's excellent post on using DBCC CLEANTABLE to reclaim space left over after a column with a variable length type has been dropped from a table.

According to Books OnLine, the variable length data types are VARCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY, VARBINARY(MAX), TEXT, NTEXT, IMAGE, SQL_VARIANT, and XML. If you drop one of these type columns from a table, SQL Server doesn't release the space it occupied; I imagine this is a speed optimization.

Thus inspired, here's a little script to run DBCC CLEANTABLE on all the user tables in the current database. You might be thinking, "How often does a column get dropped from a table, really?" The answer of course is, "It depends." On one huge (and abused) database I ran this script on, I gained 8 GB of space!

-----------------------------------------------------------------------------
-- Reclaim space from dropped variable length columns and text columns.      
-----------------------------------------------------------------------------

RAISERROR('Reclaiming space from dropped variable length columns and text columns...', 10, 1) WITH NOWAIT

DECLARE @TableName  sysname       = ''
DECLARE @SchemaName sysname       = ''

DECLARE cur CURSOR FOR 
    SELECT SCHEMA_NAME(schema_id)   AS 'SchemaName' 
         , name                     AS 'TableName'
      FROM sys.tables
     WHERE is_ms_shipped = 0
       AND type_desc     = 'USER_TABLE'
  ORDER BY 'SchemaName', 'TableName'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName        

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TableName = @SchemaName + '.' + @TableName
    RAISERROR('   Examining table %s', 10, 1, @TableName) WITH NOWAIT
    DBCC CLEANTABLE(0, @TableName, 100000) WITH NO_INFOMSGS
    FETCH NEXT FROM cur INTO @SchemaName, @TableName        
END

CLOSE cur
DEALLOCATE cur

RAISERROR('Done.', 10, 1) WITH NOWAIT

Note that we specify a "batch_size" value of 100,000 rows per transaction to keep the logfile small while processing, and to keep the table locks (!) short. The appropriate value for a given system will require some experimentation: for mine, it stopped getting faster at 100,000 rows. Also, BOL points out that this script does not need to be run on a regular basis, just when a variable length or text column is dropped: but how often that actually is depends on your environment.

Deleting User Objects in Master Database

This script erases everything in the master database that is not Microsoft-shipped. Be careful: some user objects belong in the master database (database triggers, security objects, for example). Backing up the master database first would be a prudent step.

-- Drops all user objects that have been accidentally created in the master database.
USE master

DECLARE @sName     sysname
DECLARE @sTypeDesc NVARCHAR(1024)
DECLARE @sType     NVARCHAR(1024)
DECLARE @nScore    INT
DECLARE @sSql      NVARCHAR(1024)

DECLARE TheCursor CURSOR FOR
 SELECT name, type_desc
   FROM sys.objects
  WHERE is_ms_shipped = 0

OPEN TheCursor

FETCH NEXT FROM TheCursor
 INTO @sName, @sTypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sType  = REVERSE(@sTypeDesc)
 SET @nScore = CHARINDEX('_', @sType)
 SET @sType  = SUBSTRING(@sType, 0, @nScore)
 SET @sType  = REVERSE(@sType)

 SET @sSql = 'DROP ' + @sType + ' ' + @sName
 RAISERROR('%s', 10, 1, @sSql) WITH NOWAIT 
 EXEC sp_sqlexec @sSql
 
 FETCH NEXT FROM TheCursor
  INTO @sName, @sTypeDesc
END

CLOSE TheCursor
DEALLOCATE TheCursor

PRINT 'Done.'

Find and Fix NOT NULL Columns Without DEFAULT Constraints

The latest and greatest version of my FindAllNotNullColumnsWithNoDefaultConstraint.sql script (formerly, embarrassingly, FindAllNonNullableColumnsWithNoDefaultConstraint.sql) is now available. It returns the DML code to add DEFAULT constraints to all NOT NULL columns that don't already have them. Useful on legacy databases to head off problems before they happen.

This is a work in progress, so the usual caveats apply: do not run this on any database you want to keep, for example, a production database, or any database that isn't backed up. Comments welcome!

Find NOT NULL Columns Without DEFAULT Constraints

This is really handy when you have a large legacy database where the previous owner forgot to add DEFAULT constraints to the NOT NULL columns.

This script has been moved to the Schema Analysis scripts page.