需求:將資料庫中屬於舊結構描述 A 的所有物件(表、視圖、預存程序等)移轉至新結構描述 B,並清除舊使用者/結構描述。
-- 所有使用者資料表
SELECT name FROM sys.tables WHERE schema_id = SCHEMA_ID('A');
-- 或舊版相容
SELECT id, name FROM sysobjects WHERE type = 'U' AND uid = USER_ID('A');
-- 所有物件(含視圖、SP、函數...)
SELECT o.name, s.name AS SchemaName, o.type_desc
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'A';
ALTER SCHEMA 單次僅能轉移一個物件,需動態產生批次指令:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER SCHEMA [B] TRANSFER [A].[' + o.name + N'];' + CHAR(13)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'A'
AND o.type IN ('U','V','P','FN','IF','TF','TR'); -- 表、視圖、SP、函數、觸發器
-- 列印檢查
PRINT @sql;
-- 執行
EXEC sp_executesql @sql;
CREATE USER [UserB] FOR LOGIN [LoginB];
CREATE SCHEMA [B] AUTHORIZATION [UserB];
A 權限授予 UserB,移除 UserA 權限。DROP USER [UserA];
DROP SCHEMA [A];
ALTER SCHEMA [dbo] TRANSFER [danxiao1].[ds_city1];
善用 sys.objects/sys.schemas 中繼資料與動態 SQL,可輕鬆完成大規模結構描述重構。