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!
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
