SQL Server 元數據獲取三大途徑：系統存儲過程、信息架構視圖、系統表深度對比

元數據是「數據的數據」，描述資料庫對象的結構與屬性。在 SQL Server 中，獲取元數據主要有三條路徑：系統存儲過程/函數（封裝層）、信息架構視圖（標準層）、系統表（底層）。本文詳細對比三者優劣、適用場景，並提供實戰腳本與一個自動加密存儲過程的綜合案例。

一、 系統存儲過程與函數：最常用的封裝接口

優點：抽象了系統表結構，版本兼容性好，語義清晰。
缺點：結果集難以直接嵌入 T-SQL 邏輯（需插入臨時表轉存）。

常用存儲過程

常用系統函數

結果集轉存技巧

CREATE TABLE #sp_result (DATABASE_NAME sysname, DATABASE_SIZE int, REMARKS varchar(254));
INSERT INTO #sp_result EXEC ('sp_databases');

二、 信息架構視圖：ANSI SQL-92 標準，跨平台首選

視圖形式，可直接 SELECT、JOIN、WHERE，無需臨時表。系統表結構變更不影響視圖定義，應用遷移性最強。

核心視圖清單

• INFORMATION_SCHEMA.TABLES：表/視圖基本信息
• INFORMATION_SCHEMA.COLUMNS：列詳情（最常用）
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE：主鍵/外鍵列
• INFORMATION_SCHEMA.CHECK_CONSTRAINTS：檢查約束
• INFORMATION_SCHEMA.VIEWS：視圖定義
• INFORMATION_SCHEMA.SCHEMATA：數據庫列表

實戰範例

-- 獲取某表列數
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'mytable';

-- 獲取所有用戶表名
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo';

三、 系統表：底層真相，特殊場景必備

直接查詢系統表可獲取視圖未暴露的深層信息（如 syscomments 存儲對象原始 SQL 文本），但版本升級可能導致列名/含義變更，僅在前兩層無法滿足時使用。

高頻系統表

實戰範例

-- 1. 獲取所有用戶表名（排除系統表）
SELECT OBJECT_NAME(id) FROM sysobjects 
WHERE xtype = 'U' AND OBJECTPROPERTY(id, 'IsMSShipped') = 0;

-- 2. 獲取指定表所有索引名
SELECT name FROM sysindexes 
WHERE id = OBJECT_ID('mytable') AND indid > 0;

四、 綜合實戰：自動加密所有用戶存儲過程

利用 sysobjects 篩選用戶存儲過程（xtype='P' AND type='P'），讀取 syscomments.text 獲取原始定義，將 AS 替換為 WITH ENCRYPTION AS，再 DROP + EXEC 重建。

DECLARE @sp_name nvarchar(400), @sp_content nvarchar(2000), @asbegin int, @now datetime;
SELECT @now = GETDATE();

DECLARE sp_cursor CURSOR FOR
SELECT OBJECT_NAME(id) FROM sysobjects
WHERE xtype = 'P' AND type = 'P' AND crdate < @now AND OBJECTPROPERTY(id, 'IsMSShipped') = 0;

OPEN sp_cursor;
FETCH NEXT FROM sp_cursor INTO @sp_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name);
    SELECT @asbegin = PATINDEX('%AS' + CHAR(13) + '%', @sp_content);
    SELECT @sp_content = SUBSTRING(@sp_content, 1, @asbegin - 1) + ' WITH ENCRYPTION AS' 
                         + SUBSTRING(@sp_content, @asbegin + 2, LEN(@sp_content));
    EXEC sp_executesql 'DROP PROCEDURE [' + @sp_name + ']';
    EXEC sp_executesql @sp_content;
    FETCH NEXT FROM sp_cursor INTO @sp_name;
END
CLOSE sp_cursor; DEALLOCATE sp_cursor;

五、 選型建議

• 日常開發/報表/工具：首選 INFORMATION_SCHEMA 視圖，標準、可組合、穩定。
• 快速交互式查詢/管理腳本：系統存儲過程，語義豐富、輸出格式化好。
• 深度診斷/底層屬性/跨版本兼容性敏感邏輯：謹慎使用 系統表，並做好版本適配測試。

三層架構各司其職，靈活組合才能高效掌控元數據。