Dynamic alter table script to make all column NULL in sql server 2005/2008

Wednesday, August 26, 2009 |


I have read question in few different forums many time that “How to make all column null in one table?” 
Answer is very small and handy dynamic script which uses “Information_Schema.columns” view to get column name and generate Alter Table script. Have a look:


select 'alter table ' + ic.TABLE_SCHEMA + '.' + ic.TABLE_NAME + ' alter column ' + ic.COLUMN_NAME + '  ' + ic.DATA_TYPE 
+ case when ic.DATA_TYPE='varchar' then + '(' + cast(ic.CHARACTER_MAXIMUM_LENGTH AS varchar(3)) + ')' else ' ' end + ' ' + 'null'
 from INFORMATION_SCHEMA.COLUMNS as ic
 left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as icu on ic.TABLE_NAME=icu.TABLE_NAME and ic.COLUMN_NAME=icu.COLUMN_NAME
 where ic.TABLE_NAME='YourTableName' and COLUMNPROPERTY(object_id(ic.table_name),ic.column_name,'IsIdentity')=0
 and icu.TABLE_NAME is null

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: