將所有資料表中名為 aaa 的欄位,統一修改為 varchar(20)。
DECLARE @sql VARCHAR(1000);
DECLARE cur_sql CURSOR FOR
SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(a.object_id)) + '.' + QUOTENAME(a.name)
+ ' ALTER COLUMN ' + QUOTENAME(b.name) + ' VARCHAR(20) '
FROM sys.objects a
JOIN sys.columns b ON a.object_id = b.object_id
WHERE b.name = 'aaa' AND a.type = 'U'; -- U = 使用者資料表
OPEN cur_sql;
FETCH NEXT FROM cur_sql INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql; -- 列印供審核
EXEC (@sql); -- 執行
FETCH NEXT FROM cur_sql INTO @sql;
END
CLOSE cur_sql;
DEALLOCATE cur_sql;
SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(a.object_id)) + '.' + QUOTENAME(a.name)
+ ' ALTER COLUMN ' + QUOTENAME(b.name) + ' VARCHAR(20);'
FROM sys.objects a
JOIN sys.columns b ON a.object_id = b.object_id
WHERE b.name = 'aaa' AND a.type = 'U';
將結果複製到新查詢視窗,確認無誤後批次執行。
QUOTENAME 防止識別碼注入與特殊字元錯誤。OBJECT_SCHEMA_NAME。