Wednesday, April 7, 2010

Want to change several columns to NULL

I have a few columns in three tables and want to convert them to nullable column. Below is the script

 

DECLARE @tbl_name varchar(255)
DECLARE @col_name varchar(255)
DECLARE @dt_type varchar(255)
DECLARE @alterCommand varchar(255)

if exists (
select
table_name,
column_name ,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'NO'
and TABLE_NAME like '%ERR'
)
begin

DECLARE TblCursor CURSOR FOR

select
table_name,
column_name ,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'NO'
and TABLE_NAME like '%ERR'

OPEN TblCursor
FETCH next FROM TblCursor
INTO @tbl_name,@col_name,@dt_type

WHILE @@fetch_status=0
BEGIN
    SET @altercommand = 'alter table ' + @tbl_name +
    ' alter column '+@col_name+' '+@dt_type+' null '
    EXECUTE(@dropcommand)
    FETCH next FROM TblCursor INTO @tbl_name,@col_name,@dt_type
end

end
CLOSE TblCursor
DEALLOCATE TblCursor

No comments:

Post a Comment