SQL Server 查詢所有資料表並批次變更結構描述 (Schema) 完整教學

常用系統目錄查詢 (SQL 2005+ 建議使用 sys.objects)

1. 列出所有使用者資料表

SELECT name FROM sys.tables ORDER BY name; -- 或舊版 sysobjects WHERE type = 'U'

2. 查詢單一資料表所有欄位

SELECT c.name, t.name AS type_name, c.max_length, c.precision, c.scale, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.YourTable');

批次變更 Schema 實戰：將 Schema A 物件移至 Schema B

情境：資料庫 testDB 現有 Schema A / User A，需全數移至 B 並刪除 A。

前置作業

1. 建立 User B 與 Schema B：
   CREATE USER B FOR LOGIN B; CREATE SCHEMA B AUTHORIZATION B;
2. 授與 Schema B 權給 User B，收回 User A 對 Schema A 權限，刪除 User A。

核心指令：ALTER SCHEMA TRANSFER

-- 單一物件
ALTER SCHEMA [B] TRANSFER A.[TableName];
ALTER SCHEMA [B] TRANSFER A.[ViewName];
ALTER SCHEMA [B] TRANSFER A.[ProcName];

動態產生批次腳本 (一次處理所有物件)

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER SCHEMA [B] TRANSFER A.' + QUOTENAME(name) + N';' + CHAR(13)
FROM sys.objects
WHERE schema_id = SCHEMA_ID('A')
  AND type IN ('U','V','P','FN','IF','TF'); -- 表、檢視、預存程序、函數

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

清理舊 Schema

-- 確認無殘留物件
SELECT * FROM sys.objects WHERE schema_id = SCHEMA_ID('A');
-- 刪除 Schema
DROP SCHEMA [A];

範例：將 danxiao1.ds_city1 移至 dbo

ALTER SCHEMA [dbo] TRANSFER danxiao1.ds_city1;

注意事項

• TRANSFER 不會改變物件權限，若有獨立授權需同步調整。
• 參考此物件的外部程式 (連結伺服器、SSIS、應用程式) 需同步更新 Schema 前綴。
• 系統物件 (type = 'S') 不可移動。
• SQL 2000 不支援 ALTER SCHEMA TRANSFER，需用 sp_changeobjectowner (已淘汰)。