Data Warehousing Tip: T-SQL Script to Create DEFAULT Constraints by Data Type

There is an ongoing discussion/debate regarding the handling of NULLS in a data warehouse, however, the following article by Michelle Poolet summarizes it beautifully – “consider the data warehouse business requirements” (see http://sqlmag.com/database-administration/null-data-warehouse).

Beyond consideration, there are some data types that should get the DEFAULT treatment (bit, char, nchar, nvarchar, and varchar) and the following script can assist in creating these across every applicable column in your database.

SELECT Object_schema_name(T.[object_id], Db_id()) AS [Schema], 
       T.[name]  AS [table_name], 
       AC.[name] AS [column_name], 
       TY.[name] AS system_data_type, 
       AC.[max_length], 
       AC.[precision], 
       AC.[scale], 
       AC.[is_nullable], 
       AC.[is_ansi_padded], 
       'ALTER TABLE dbo.' + T.[name] 
       + ' ADD CONSTRAINT ' + AC.[name] + ' DEFAULT(' 
       + CASE TY.[name] 
            --WHEN 'int' THEN '0' 
          --WHEN 'bigint' THEN '0' 
          WHEN 'varchar' THEN '' 
          WHEN 'bit' THEN '' 
          WHEN 'char' THEN '' 
          WHEN 'smalldatetime' THEN '' 
          WHEN 'datetime' THEN ''
          --WHEN 'tinyint' THEN '0'
          --WHEN 'smallint' THEN '0' 
          WHEN 'nvarchar' THEN '' 
          --WHEN 'money' THEN '0.00'
          ELSE '' 
       END +') FOR '+ac.name AS sqlstring 
   FROM   sys.[tables] AS T 
       INNER JOIN sys.[all_columns] AC 
               ON T.[object_id] = AC.[object_id] 
       INNER JOIN sys.[types] TY 
               ON AC.[system_type_id] = TY.[system_type_id] 
                  AND AC.[user_type_id] = TY.[user_type_id] 
WHERE  T.[is_ms_shipped] = 0 
       AND T.[name] LIKE ('DIM%') AND AC.[name] NOT LIKE ( '%SrgKey%' )  AND 
TY.[name] in ('varchar', 'bit' , 'char', 'smalldatetime', 'datetime' , 'nvarchar')

I’ve taken the approach of defaulting values for all columns (less those affected by the WHERE clause) in my STAGING Database.  The thought is that the non-NULL values inserted into my staging tables will persist to the TARGET database and I won’t experience one of a variety of issues (OLAP, T-SQL, or otherwise) that a development and/or user team will encounter due to having NULL values in the data warehouse.