什麼是元數據？
元數據描述資料的結構與語義：資料表/檢視清單、欄位定義、約束、索引、權限等。SQL Server 提供三層查詢介面，由高到低：
- 系統預存程序/函數 — 封裝最好、版本相容性最強。
- INFORMATION_SCHEMA 檢視 — ANSI SQL-92 標準、跨平台可移植。
- 系統表 — 底層儲存、功能最全但版本相依、Microsoft 不保證結構不變。
第一層：系統預存程序與函數（推薦優先使用）
常用預存程序
| 程序 | 用途 |
|---|
| sp_columns | 指定資料表/檢視的欄位詳細資訊 |
| sp_databases | 伺服器上所有資料庫基本資訊 |
| sp_fkeys | 外鍵關係查詢（雙向） |
| sp_pkeys | 主鍵資訊 |
| sp_server_info | 伺服器屬性與設定值 |
| sp_sproc_columns | 預存程序參數定義 |
| sp_statistics | 索引與統計資料 |
| sp_stored_procedures | 預存程序清單（含系統程序） |
| sp_tables | 資料表/檢視清單（含系統表） |
常用系統函數
| 函數 | 用途 |
|---|
| COLUMNPROPERTY | 欄位屬性（是否身分欄、允許 NULL、計算欄等） |
| DATABASEPROPERTYEX | 資料庫選項狀態（復原模式、相容性等級等） |
| OBJECT_ID / OBJECT_NAME | 物件名稱與 ID 互轉 |
| OBJECTPROPERTY | 物件屬性（是否表、是否約束、是否系統物件等） |
| fn_listextendedproperty | 擴充屬性（MS_Description 等自訂文件） |
進階技巧：將預存程序結果存入暫存表
CREATE TABLE #dbs (name sysname, size int, remarks varchar(254));
INSERT INTO #dbs EXEC sp_databases;
SELECT * FROM #dbs WHERE size > 100;
第二層：INFORMATION_SCHEMA 檢視（標準化、易用）
所有檢視均為 `INFORMATION_SCHEMA.*`，可直接 `SELECT`、加 `WHERE`、參與 `JOIN`。
| 檢視 | 內容 |
|---|
| CHECK_CONSTRAINTS | CHECK 約束定義 |
| COLUMNS | 所有可存取欄位完整定義 |
| CONSTRAINT_COLUMN_USAGE | 欄位-約束對應 |
| CONSTRAINT_TABLE_USAGE | 資料表-約束對應 |
| KEY_COLUMN_USAGE | 主鍵/外鍵欄位 |
| SCHEMATA | 資料庫清單 |
| TABLES | 資料表/檢視清單 |
| VIEWS | 檢視定義與擁有者 |
範例：取得指定資料表欄位數
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' AND TABLE_SCHEMA = 'dbo';
第三層：系統表（深度診斷、特殊需求）
| 系統表 | 核心內容 |
|---|
| syscolumns | 每個欄位/參數的定義 |
| syscomments | 檢視/規則/預設/觸發器/預存程序原始 SQL 文本 |
| sysconstraints | 約束基本資訊 |
| sysdatabases | 資料庫基本資訊 |
| sysindexes | 索引與統計資料 |
| sysobjects | 所有物件總表（xtype 區分類型） |
| sysreferences | 外鍵欄位關係 |
| systypes | 系統與使用者定義資料類型 |
綜合實戰範例
1. 列出所有使用者資料表名稱
SELECT OBJECT_NAME(id) AS TableName
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY(id, 'IsMSShipped') = 0;
2. 取得指定資料表所有索引名稱
SELECT name FROM sysindexes
WHERE id = OBJECT_ID('MyTable') AND indid > 0 AND indid < 255;
3. 自動加密所有使用者預存程序（SQL 2000/2005 相容）
DECLARE @sp_name nvarchar(400), @sp_content nvarchar(max), @as_pos int, @now datetime;
SELECT @now = GETDATE();
DECLARE cur CURSOR FOR
SELECT OBJECT_NAME(id) FROM sysobjects
WHERE xtype = 'P' AND type = 'P' AND crdate < @now
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0;
OPEN cur; FETCH NEXT FROM cur INTO @sp_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name);
SELECT @as_pos = PATINDEX('%AS' + CHAR(13) + '%', @sp_content);
IF @as_pos > 0
BEGIN
SET @sp_content = SUBSTRING(@sp_content, 1, @as_pos - 1) +
' WITH ENCRYPTION AS' +
SUBSTRING(@sp_content, @as_pos + 2, LEN(@sp_content));
EXEC('DROP PROCEDURE [' + @sp_name + ']');
EXEC(@sp_content);
END
FETCH NEXT FROM cur INTO @sp_name;
END
CLOSE cur; DEALLOCATE cur;
最佳實務建議
- 應用程式開發：優先使用 INFORMATION_SCHEMA 檢視，確保跨版本/跨平台相容。
- DBA 診斷/維護：善用系統函數（`OBJECTPROPERTY`、`DATABASEPROPERTYEX`）快速取得屬性。
- 特殊需求（如解析預存程序原始碼、檢查特定內部結構）：謹慎直接查詢系統表，並做版本相容性測試。
