萬盛學電腦網

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

獲取 SQL Server 中繼資料的五大標準方法

中繼資料描述資料結構與語義。開發、維護、文件化、程式碼生成都需查詢中繼資料。SQL Server 提供五層存取介面,由高層封裝至底層系統表。

方法一:系統預存程序 (最常用、資訊最豐富)

預存程序用途關鍵輸出欄位
sp_help物件總覽Name, Owner, Type, Created
sp_helptext檢視/程序/函數/觸發器定義Text
sp_columns資料表/檢視欄位詳情COLUMN_NAME, TYPE_NAME, PRECISION, SCALE, IS_NULLABLE
sp_pkeys主鍵資訊COLUMN_NAME, KEY_SEQ, PK_NAME
sp_fkeys外來鍵關聯PKTABLE_NAME, FKTABLE_NAME, PKCOLUMN, FKCOLUMN
sp_statistics索引與統計資料INDEX_NAME, TYPE_DESC, COLUMNS, ROWS, ROWS_PER_KEY
sp_tables所有資料表/檢視TABLE_NAME, TABLE_TYPE, TABLE_OWNER
sp_stored_procedures預存程序清單PROCEDURE_NAME, PROCEDURE_TYPE
sp_sproc_columns程序參數資訊PARAMETER_NAME, TYPE_NAME, PRECISION, SCALE
sp_databases伺服器上所有資料庫DATABASE_NAME, DATABASE_SIZE, REMARKS
sp_server_info伺服器屬性ATTRIBUTE_ID, ATTRIBUTE_NAME, ATTRIBUTE_VALUE

方法二:資訊結構描述檢視 (INFORMATION_SCHEMA) - ANSI 標準

  • 跨資料庫平台可移植
  • 主要檢視:TABLES, COLUMNS, VIEWS, TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS, ROUTINES, PARAMETERS
  • 範例:SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'

方法三:目錄檢視 - SQL Server 專用、效能最佳、資訊最完整

經典查詢:取得資料表完整結構文件

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length, c.precision, c.scale,
    c.is_nullable, c.is_identity,
    ISNULL(ep.value, '') AS ColumnDescription,
    CASE WHEN pk.is_primary_key = 1 THEN 'PK' ELSE '' END AS KeyType
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.extended_properties ep 
    ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.class = 1
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.indexes i
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1
) pk ON pk.object_id = c.object_id AND pk.column_id = c.column_id
WHERE t.is_ms_shipped = 0
ORDER BY t.name, c.column_id;

方法四:動態管理檢視/函數 (DMV/DMF) - 執行狀態中繼資料

  • sys.dm_exec_requests、sys.dm_exec_sessions、sys.dm_exec_query_stats、sys.dm_db_index_usage_stats、sys.dm_db_partition_stats
  • 非持久化,重啟重置,用於效能診斷

方法五:系統基底表 (sys.* 舊版相容) - 直接查詢,不建議生產使用

  • sysobjects, syscolumns, systypes, sysindexes, syscomments, sysproperties
  • 僅為相容性保留,Microsoft 不保證結構穩定

選擇指南

檢視描述
sys.objects所有物件 (表、檢視、程序、函數...)
sys.tables / sys.views篩選後的表/檢視
sys.columns欄位定義
sys.indexes索引定義
sys.partitions分割區層級儲存統計
sys.foreign_keys / sys.foreign_key_columnsFK 關聯
sys.sql_modules模組定義原始碼 (含加密物件)
sys.parameters程序/函數參數
sys.schemas結構描述
需求推薦方法
快速互動查詢物件結構sp_help / sp_columns
程式碼生成器/文件工具開發目錄檢視 (sys.*)
跨平台相容腳本INFORMATION_SCHEMA
效能監控/索引使用率分析DMV
舊版相容腳本維護系統基底表 (謹慎)

善用目錄檢視編寫中繼資料查詢,效能最佳且資訊最豐富,是現代 SQL Server 開發標準。

copyright © 萬盛學電腦網 all rights reserved