Make all columns from all tables in database, NOT NULL and set Default value in SQL Server 2008

Today I helped one person in one forum, he wanted to generate script to make all columns in all tables NOT NULL and wanted to set DEFAULT value 0 for all numeric data types. I felt to share it with all my readers as it could be interesting to know and sometime could be very helpful and handy with some customize changes.

select

not_null = 'alter table ' + table_name + ' alter column '

                  + column_name + ' ' + data_type

                  + case when data_type = 'numeric' then '(' else '' end

                  + case when data_type = 'numeric' then convert(varchar,numeric_precision_radix) else '' end

                  + case when data_type = 'numeric' then ',' else '' end

                  + case when data_type = 'numeric' then convert(varchar,numeric_scale) else '' end

                  + case when data_type = 'numeric' then ')' else '' end

                  + ' not null '

,default_0 = 'alter table ' + table_name

                  + ' add default 0 for ' + column_name

from information_schema.columns

where COLUMN_DEFAULT is null

and   data_type not like '%char%'

and   data_type not like '%time%'

and   data_type not like '%image%'

and   data_type not like '%binary%'

 

Above query will list the T-SQL script which you can run in your query editor.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com