Script to generate default values ​​for non-columns that do not have default values ​​specified in SQL Server

Question

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

I wanted to know if there is a way to generate a script that will force the default values ​​for all NOT Null columns that don't have a default. This way I can imitate MySQL's implicit defaults using explicit defaults in SQL Server

Answer 1

try something like that. this will create some SQL that you can run to add the missing default values. So run this and then take the output and run it to actually add the default values. Alternatively, you can configure what I set as the default value in 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')