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
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:
Post a Comment