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

Tuesday, May 5, 2009 |


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

0 comments: