批量修改所有表某个字段属性

[sql]代码库

DECLARE @ColumnName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @IsNull INT
DECLARE @sSQL VARCHAR(500)
DECLARE cur CURSOR FOR
SELECT o.name,c.name,c.is_nullable, c.* 
FROM sys.[columns] AS c 
LEFT JOIN sys.objects AS o ON o.[object_id]=c.[object_id]
WHERE c.name LIKE '%ItemID' AND o.[type]='U' AND c.max_length=15
OPEN cur
FETCH NEXT FROM cur INTO @TableName,@ColumnName,@IsNull
WHILE @@FETCH_STATUS=0
BEGIN
	SET @sSQL='ALTER TABLE '+@TableName+' ALTER COLUMN '+@ColumnName+' VARCHAR(100) ' + CASE WHEN @IsNull=1 THEN ' NOT NULL' ELSE '' END
	PRINT @sSQL
	
	--EXECUTE('ALTER TABLE ' + @tableName +' ADD CONSTRAINT DF_'+@tableName+'_Status DEFAULT (1) FOR [STATUS]')
	--EXECUTE(@sSQL)
	--EXECUTE ('UPDATE '+ @tableName + 'SET [Status]=1 WHERE [STATUS] IS NULL')
	
	FETCH NEXT FROM cur INTo @TableName,@ColumnName,@IsNull
END
CLOSE cur
DEALLOCATE cur