Script to generate default values for not columns with no defaults specified in SQL Server

Question

I have a MySQL database that I am migrating to SQL Server. The application that references the database breaks when I run it against SQL Server because SQL Server does not offer the implicit defaults that MySQL offers.

I wanted to see if there is a way to generate script that would force defaults on all NOT Null columns that do not have a default specified. This way I can mimic MySQL's implicit defaults using explicit defaults in SQL Server

Answer 1

try something like this. this will generate some SQL that you can run to add the missing defaults. So run this and then take the output and run that to actually add the defaults. Also, you may want to tweak what I set as the defaults in the CASE:

SELECT
'ALTER TABLE '+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME
    +' ADD CONSTRAINT DF_'+c.TABLE_NAME+'_'+c.COLUMN_NAME+'_'+CONVERT(varchar(5),c.ORDINAL_POSITION)
    +' DEFAULT '
        +CASE c.DATA_TYPE
             WHEN 'money'         THEN '0.0'
             WHEN 'int'           THEN '0'
             WHEN 'text'          THEN ''''''
             WHEN 'smallint'      THEN '0'
             WHEN 'datetime'      THEN 'GETDATE()'
             WHEN 'varchar'       THEN ''''''
             WHEN 'numeric'       THEN '0'
             WHEN 'tinyint'       THEN '0'
             WHEN 'smalldatetime' THEN 'GETDATE()'
             WHEN 'float'         THEN '0.0'
             WHEN 'char'          THEN ''''''
             WHEN 'bigint'        THEN '0'
             WHEN 'bit'           THEN '0'
             WHEN 'nvarchar'      THEN ''''''
         END
        +' FOR '+c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS    c
    WHERE c.COLUMN_DEFAULT IS NULL AND IS_NULLABLE='NO'
        AND c.DATA_TYPE NOT IN ('image','varbinary','binary')