Supply default values to all columns, tables, databases in sql-2012 express

Question

I am working on the sql 2012 server express edition.

I want to assign the default values to all columns of all tables in all user databases.

1. All numeric, int, smallint, tinyint etc 0 default value
2. All date related fields '01-jan-1900' default value
3. All character fields (Varchar etc) '' default value
4. All Logical fields 0 default value

I do not want to do it with table designer. How to do it with command or stored procedure.

This default value is not to be set for the primary key fields and auto increment fields (Identity Columns).

I am new to SQL programming and all the times I have to take care for the NULL values and for my working the NULL have no significance other than thier default values. It is to be done for the all existing tables.

Answer 1

You can try a solution like this for each database:

DECLARE @SQL nvarchar(max) = ''

SELECT
    @SQL = @SQL +
    'UPDATE [' + s.name + '].[' + o.name + '] SET [' + c.name + '] = ' +
    CASE
        WHEN t.name IN ('int', 'bigint', 'bit', 'decimal', 'money', 'numeric', 'smallint', 'smallmoney', 'tinyint', 'float', 'real') THEN '0'
        WHEN t.name IN ('char', 'varchar', 'text') THEN ''''''
        WHEN t.name IN ('nchar', 'nvarchar', 'ntext') THEN 'N'''''
        WHEN t.name IN ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') THEN 'CAST(''1900-01-01'' AS ' + t.name + ')'
        ELSE '0'
    END +
    ' WHERE [' + c.name + '] IS NULL; '
FROM
    sys.schemas s 
    INNER JOIN
    sys.objects o ON s.[schema_id] = o.[schema_id]
    INNER JOIN
    sys.columns c ON o.[object_id] = c.[object_id]
    INNER JOIN
    sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE
    o.[type] = 'U'
    AND
    c.is_identity <> 1
    -- If you want to apply it to only one table use this:
    AND
    o.name = 'yourTable'

EXEC(@SQL)