循环更新某些表中的字段类型和长度

DECLARE @TableName NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)
DECLARE @DataType NVARCHAR(128)
DECLARE @Length INT

-- 设置你要更新的数据类型和长度
SET @DataType = 'VARCHAR'
SET @Length = 100

-- 获取所有表的名称
DECLARE TableCursor CURSOR FOR
SELECT distinct TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'pihao';

 

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- 设置列名
SET @ColumnName = 'pihao';

-- 更新表结构
EXEC('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' varchar(200)' )

-- 获取下一个表
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor