SQL Server 2005 批量變更資料表與預存程序 Schema (擁有者) 的完整指令集

核心系統預存程序：sp_changeobjectowner

sp_changeobjectowner @objname = '舊Schema.物件名', @newowner = '新Schema';

• 適用於資料表、檢視表、預存程序、函數等。
• 執行後該物件所有權限清空，需重新授權。

批量變更資料表 Schema：兩種方法

方法一：游標逐一處理（可控、可記錄）

DECLARE @name SYSNAME;
DECLARE csr CURSOR FOR
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '舊Schema';  -- 可加篩選
OPEN csr;
FETCH NEXT FROM csr INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_changeobjectowner @objname = '舊Schema.' + @name, @newowner = '新Schema';
    FETCH NEXT FROM csr INTO @name;
END
CLOSE csr; DEALLOCATE csr;

方法二：sp_MSforeachtable 一鍵搞定（快、但不可回滾單一失敗）

EXEC sp_MSforeachtable @command1 = "EXEC sp_changeobjectowner '?', '新Schema'";

? 會自動代入「Schema.資料表名」。

批量變更預存程序 Schema

sp_changeobjectowner 對程序也適用，但較推薦使用 ALTER SCHEMA ... TRANSFER（SQL 2005+ 標準語法）：

-- 產生所有 ALTER SCHEMA 指令
SELECT 'ALTER SCHEMA 新Schema TRANSFER ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name)
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE s.name = '舊Schema';

複製結果在 SSMS 執行即可。同理可處理函數、檢視表、資料表（只需換 sys.tables、sys.views 等目錄檢視）。

完整自動化腳本範例（資料表+程序+函數+檢視）

DECLARE @oldSchema SYSNAME = '舊Schema', @newSchema SYSNAME = '新Schema';
DECLARE @sql NVARCHAR(MAX) = N'';

-- Tables
SELECT @sql += 'ALTER SCHEMA ' + QUOTENAME(@newSchema) + ' TRANSFER ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = @oldSchema;

-- Views
SELECT @sql += 'ALTER SCHEMA ' + QUOTENAME(@newSchema) + ' TRANSFER ' + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ';'
FROM sys.views v JOIN sys.schemas s ON v.schema_id = s.schema_id WHERE s.name = @oldSchema;

-- Procedures
SELECT @sql += 'ALTER SCHEMA ' + QUOTENAME(@newSchema) + ' TRANSFER ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ';'
FROM sys.procedures p JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE s.name = @oldSchema;

-- Functions
SELECT @sql += 'ALTER SCHEMA ' + QUOTENAME(@newSchema) + ' TRANSFER ' + QUOTENAME(s.name) + '.' + QUOTENAME(f.name) + ';'
FROM sys.objects f JOIN sys.schemas s ON f.schema_id = s.schema_id WHERE s.name = @oldSchema AND f.type IN ('FN','IF','TF');

PRINT @sql;  -- 確認後再 EXEC
-- EXEC sp_executesql @sql;

注意事項

1. 執行前完整備份資料庫，並腳本化現有權限以便事後還原。
2. Schema 變更會導致相依物件（如檢視表、函數、同義詞）失效，需 sp_refreshview / sp_refreshsqlmodule 重新整理。
3. 生產環境建議維護視窗執行，並逐類別驗證（先表、後程式）。