人人都是 DBA（II）SQL Server 中繼資料

標籤：des   style   blog   http   io   ar   color   os   使用   

SQL Server 中維護了一組表用於儲存 SQL Server 中所有的對象、資料類型、約束條件、配置選項、可用資源等資訊，這些資訊稱為中繼資料資訊（Metadata），而這些表稱為系統基礎資料表（System Base Tables）。在這些基礎資料表中，存在於 master 資料庫中的一部分基礎資料表包含系統級範圍的資訊。存在於特定資料庫（也包含 master db）中的基礎資料表包含屬於該特定資料庫的對象和資源資訊。

使用系統管理員身份登入，可以從 sys.objects 中查詢所有的系統基礎資料表。

USE master;SELECT [name] ,type_descFROM sys.objectsWHERE type_desc = ‘SYSTEM_TABLE‘ORDER BY [name];

系統基礎資料表僅在資料庫引擎內部使用，並不是提供的通用功能，所以當變化時可能無法保證相容性。這些以 "sys" 為首碼的表對象，例如 sysobjects, sysindexes, sysusers, sysdatabases 等，實際上是相容視圖（Compatibility Views）。SQL Server 通過提供相容視圖的集合來保持向後相容（Backward Compatibility），使得基於這些相容視圖構建的應用程式不會被破壞。而新增的特性，例如表分區、資源調控等將不會被添加到這些視圖中。

目前，SQL Server 推薦的訪問中繼資料資訊的常規介面是目錄檢視（Catalog Views）。所有的目錄檢視，包括動態管理檢視 DMV（Dynamic Management Views）和相容視圖（Compatibility Views），均存在於 "sys" Schema 中。

SELECT SCHEMA_NAME(schema_id) AS schema_name    ,[name] AS object_name    ,type_descFROM sys.all_objectsWHERE SCHEMA_NAME(schema_id) = ‘sys‘    AND type_desc = ‘VIEW‘ORDER BY object_name;

名稱為 "sys.dm_" 開頭的對象即為動態管理檢視 DMV（Dynamic Management Views）。這些動態管理對象中既有視圖也有函數，但大部分是視圖，所以通常稱為動態管理檢視 DMV（Dynamic Management Views）。DMV 不是基於資料庫檔案的真實的表，而是基於內部資料庫結構的展現。根據 DMV 對象展示的資訊的職責範圍，它們被分成若干個目錄。

• dm_exec_* 包含與使用者代碼執行和 Session 相關的資訊。
• dm_os_* 包含低層系統資訊，如 Memory、Locking、Scheduler 等。
• dm_tran_* 包含事務的細節資訊。
• dm_io_* 包含網路和磁碟 I/O 活動相關的資訊。
• dm_db_* 包含資料庫和資料庫物件的細節資訊，如 Index 等。

目錄檢視（Catalog Views）是構建在繼承模型（Inheritance Model）上的，使得對象的通用屬性不必在內部進行重複定義。例如 sys.objects 包含了各種物件類型的通用屬性，而 sys.tables 繼承自 sys.objects，所以首先包含有與 sys.objects 完全相同的列，然後再添加特定物件類型的附加列。

可以在 SQL Server 2008 R2 System Views Map 位置下載目錄檢視的映射關係圖。

如果要查詢某個目錄檢視的定義，可以使用 object_definition 函數或 sp_helptext 進行查詢。

SELECT object_definition (object_id(‘sys.tables‘));

EXEC sp_help ‘sys.tables‘;EXEC sp_helptext ‘sys.tables‘;

訪問 SQL Server 中繼資料資訊還有一些其他方式：

• 資訊架構視圖（Information Schema Views）
• 系統函數（System Functions）
• 系統預存程序（System Stored Procedures）

例如，通過系統函數查詢指定資料庫的恢複模型。

SELECT SERVERPROPERTY(‘Edition‘) AS Edition    ,SERVERPROPERTY(‘EngineEdition‘) AS EngineEdition    ,DATABASEPROPERTYEX(‘msdb‘, ‘Recovery‘) AS RecoveryModel;

目錄檢視（Catalog Views）是訪問 SQL Server 中繼資料資訊的首選介面，其次是系統函數（System Functions）。

 

本系列文章《人人都是 DBA》由 Dennis Gao 發表自部落格園個人技術部落格，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。

人人都是 DBA（II）SQL Server 中繼資料