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

i working on sql 2012 server express edition.

i want assign default values columns of tables in 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 not want table designer. how command or stored procedure.

this default value not set primary key fields , auto increment fields (identity columns).

i new sql programming , times have take care null values , working null have no significance other thier default values. done existing tables.

you can try solution 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') '0'         when t.name in ('char', 'varchar', 'text') ''''''         when t.name in ('nchar', 'nvarchar', 'ntext') 'n'''''         when t.name in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') 'cast(''1900-01-01'' ' + t.name + ')'         else '0'     end +     ' [' + c.name + '] null; '     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 , c.user_type_id = t.user_type_id     o.[type] = 'u'     ,     c.is_identity <> 1     -- if want apply 1 table use this:     ,     o.name = 'yourtable'  exec(@sql)