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