萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 查詢所有資料表並批次變更結構描述:sys.objects、ALTER SCHEMA TRANSFER 實戰

需求:將資料庫中屬於舊結構描述 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;

完整遷移流程

  1. 建立新使用者/結構描述 B:
    CREATE USER [UserB] FOR LOGIN [LoginB];
    CREATE SCHEMA [B] AUTHORIZATION [UserB];
  2. 授權:將舊結構描述 A 權限授予 UserB,移除 UserA 權限。
  3. 批次轉移物件:執行上述動態 SQL。
  4. 清理:
    DROP USER [UserA];
    DROP SCHEMA [A];

單一物件轉移範例

ALTER SCHEMA [dbo] TRANSFER [danxiao1].[ds_city1];

注意事項

  • 轉移不改變物件權限(權限隨物件走),但擁有者變更為目標結構描述擁有者。
  • 外鍵、預設值、檢查約束等相依物件會自動隨表移動。
  • 同義詞、資料庫鏈結等跨資料庫物件需手動處理。
  • 大量物件轉移建議在維護視窗執行,並先備份。

善用 sys.objects/sys.schemas 中繼資料與動態 SQL,可輕鬆完成大規模結構描述重構。

copyright © 萬盛學電腦網 all rights reserved