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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.