SQL Server 的系統視圖是資料庫管理員與開發人員洞察資料庫內部運作、監控效能、排查問題的關鍵工具。本文將系統性整理 SQL Server 2008 及後續版本中核心系統視圖的分類、用途與查詢技巧,幫助您從宏觀架構到微觀物件快速定位所需元數據。
SQL Server 將所有物件資訊(資料表、視圖、預存程序、函數、觸發器、索引、約束等)集中存放於系統目錄中。早期版本透過 sysobjects、syscolumns 等系統表直接暴露,但結構鬆散、命名不一、難以擴展。自 SQL Server 2005 起,微軟重構為系統視圖體系,優勢在於:
sys. 前綴,分類清晰(如 sys.tables、sys.indexes)。sysobjects 等舊視圖別名,舊腳本無需立即重寫。以下表格整理自 sys.objects 的 type 欄位代碼,對應常見物件類型與主要關聯視圖:
| 代碼 | 物件類型 | 說明 | 主要關聯視圖 |
|---|---|---|---|
| U | USER_TABLE | 使用者資料表 | sys.tables, sys.columns, sys.indexes |
| V | VIEW | 視圖 | sys.views, sys.sql_modules |
| P | SQL_STORED_PROCEDURE | SQL 預存程序 | sys.procedures, sys.sql_modules |
| FN / IF / TF | SQL_SCALAR_FUNCTION / SQL_INLINE_TABLE_VALUED_FUNCTION / SQL_TABLE_VALUED_FUNCTION | 純量/內聯/多語句表值函數 | sys.sql_modules |
| TR | SQL_TRIGGER | DML 觸發器 | sys.triggers, sys.sql_modules |
| PK / FQ / UQ / C / D | PRIMARY_KEY / FOREIGN_KEY / UNIQUE / CHECK / DEFAULT_CONSTRAINT | 各類約束 | sys.key_constraints, sys.check_constraints, sys.default_constraints |
| IT | INTERNAL_TABLE | 內部表(如 XML 索引、Service Broker 隊列) | sys.internal_tables |
| S | SYSTEM_TABLE | 系統基底表 | sys.objects (type='S') |
| SN | SYNONYM | 同義詞 | sys.synonyms |
| PC / FS / FT / TA | CLR_STORED_PROCEDURE / CLR_SCALAR_FUNCTION / CLR_TABLE_VALUED_FUNCTION / CLR_TRIGGER | CLR 組件物件 | sys.assemblies, sys.assembly_modules |
| PG | PLAN_GUIDE | 計畫指南 | sys.plan_guides |
| X | EXTENDED_STORED_PROCEDURE | 擴展預存程序 | sys.extended_procedures |
-- 查詢某資料庫所有使用者資料表及其欄位、索引數量
SELECT
t.name AS TableName,
COUNT(DISTINCT c.column_id) AS ColumnCount,
COUNT(DISTINCT i.index_id) AS IndexCount
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
LEFT JOIN sys.indexes i ON i.object_id = t.object_id
WHERE t.type = 'U'
GROUP BY t.name
ORDER BY TableName;
所有「含有程式碼」的物件(預存程序、函數、觸發器、視圖、預設值、規則、計畫指南)的原始 T-SQL 定義均儲存於 sys.sql_modules.definition(nvarchar(max)),取代舊版 syscomments 的 4000 字元限制。
-- 搜尋包含特定關鍵字的預存程序/函數
SELECT
o.name AS ObjectName,
o.type_desc AS ObjectType,
m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%NOLOCK%'
AND o.type IN ('P','FN','IF','TF','TR','V');
DMV 不屬於 sys.objects 目錄,但才是效能調校的主戰場。常用分類:
sys.dm_exec_requests、sys.dm_exec_sessions、sys.dm_exec_connectionssys.dm_exec_query_stats、sys.dm_exec_cached_plans、sys.dm_exec_query_plansys.dm_db_index_usage_stats、sys.dm_db_index_physical_statssys.dm_os_wait_stats、sys.dm_os_waiting_tasks、sys.dm_tran_lockssys.dm_os_memory_clerks、sys.dm_io_virtual_file_stats、sys.dm_os_schedulersSELECT
t.name AS TableName,
p.rows AS RowCount,
(SUM(a.total_pages) * 8) / 1024.0 AS TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024.0 AS UsedSpaceMB,
COUNT(c.column_id) AS ColumnCount
FROM sys.tables t
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.type = 'U' AND i.index_id IN (0,1) -- heap 或 clustered
GROUP BY t.name, p.rows
ORDER BY TotalSpaceMB DESC;
SELECT
DB_NAME() AS DatabaseName,
o.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
s.user_seeks + s.user_scans + s.user_lookups AS TotalReads,
s.user_updates AS TotalWrites,
s.last_user_seek, s.last_user_scan, s.last_user_lookup
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
JOIN sys.objects o ON o.object_id = i.object_id
WHERE o.type = 'U'
AND s.database_id = DB_ID()
AND (s.user_seeks + s.user_scans + s.user_lookups) = 0
AND s.user_updates > 0
ORDER BY TotalWrites DESC;
SELECT
r.session_id,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.reads, r.writes, r.logical_reads,
SUBSTRING(qt.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS RunningStatement,
qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50 -- 排除系統 session
AND r.total_elapsed_time > 30000; -- 超過 30 秒
舊版系統表(sysobjects、syscolumns、sysindexes 等)在 SQL Server 2008+ 仍可查詢,實為視圖別名,微軟不保證未來版本持續提供。建議:
sys.* 視圖與 DMV。sys.system_objects 查詢系統內建物件,避免誤判使用者物件。系統視圖是 SQL Server 的「X 光機」與「儀表板」。熟練掌握 sys.objects 靜態目錄、sys.sql_modules 程式碼倉庫、sys.dm_* 動態管理視圖三大體系,即可覆蓋 90% 以上的日常開發、運維、效能調校場景。建議收藏本文速查表,並在實際環境中多動手練習上述範例查詢,將元數據轉化為可執行的優化行動。