探索SQL Server元數據（一）

簡介

　　在資料庫中，我們除了存儲數據外，還存儲了大量的元數據。它們主要的作用就是描述資料庫怎麼建立、配置、以及各種對象的屬性等。本篇簡單介紹如何使用和查詢元數據，如何更有效的管理SQLServer 資料庫。

　　對一些有經驗的資料庫開發和管理人員而言，元數據是非常有價值的。下麵我會介紹一下簡單的原理，然後儘量用代碼的方式直接說明，畢竟“talk is cheap show me the code ”。

什麼是動態線上目錄?

　　每一個關係型資料庫系統，比如SQL Server 一定要提供關於它的結構的信息，這些信息往往需要通過sql語法來查詢。通常這些信息被保存在指定數據表的結構中。這意味著資料庫中有兩種不同的表：一是用戶自定義的表和系統表或者視圖（包含元數據）。從SQL Server 2005開始，只有視圖可以查詢了，不能直接看到數據表了。

系統視圖

這種系統表或者視圖的結合通常參考關係型資料庫理論的文獻叫做作為系統目錄或者數據字典。

在資料庫內部，有一些系統表一直追蹤資料庫中發生的每一件事情。系統表存儲像表、活動、列、索引等事情。這些完全符合Edgar Codd 的關係型資料庫試試的十三條準則直譯。這個準則就是定義動態線上目錄，它就是“關於數據的數據”，也叫作元數據。

 Edgar Codd  準則4, 描述如下：

‘The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.’

翻譯：像普通數據一樣，在邏輯層的數據表達了對資料庫的描述，以便於授權用戶能應用相同的SQL語言來查詢元數據，就如同查詢常規數據一樣。

在SQL Server中，可以通過系統視圖或者架構視圖直接訪問動態線上目錄，方便用戶更為快捷的開發和管理資料庫。

如何獲得以上信息?

因為我們不能直接訪問，需要使用視圖和函數來看這些信息。只能看到你許可權內的數據。有更好的方法在用戶資料庫中使用數據定義語言(DDL)，這些DDL語句包括CREATE, DROP, ALTER, GRANT, DENY, REVOKE 和sp_rename statements 等。總有一種方法可以使用DDL來修改視圖中的任何信息，即使並不總是顯而易見的。

關係型資料庫使用動態的系統視圖中的數據描述資料庫，但是目前還有沒有標準化。但是有一個包含在每個資料庫內的架構可以讀取這些信息：就是Information Schema。

不走運的是，這個架構不足以提供足夠信息，這意味著我們需要使用SQL Server 系統資料庫的視圖和函數來補充信息。接下來需要解釋一些術語和技術，我會儘可能少的細節足以讓大家輕鬆地理解這些示例

如圖所示，如何訪問元數據，及其介面

系統視圖

Information Schema

這個架構是一套視圖，視圖中是當前資料庫的信息。每一個資料庫中都有這個架構，只能看到當前資料庫的對象信息。可以直接訪問這些架構的數據在主要的關係型數據中。其中架構視圖不包含資料庫部署信息。

對於不同的關係型資料庫之間的處理工作這個架構尤其重要。它們非常適合日常工作，例如在訪問錢檢查是否存在，但是如果需要詳細報告則會受到限制。他們還使用一種稍有不同的標準命名法:例如，資料庫被稱為目錄，用戶定義的數據類型被稱為“domain”。

之前看到MSDN上有人警告說不要使用INFORMATION_SCHEMA視圖來確認對象架構，我理解是因為SQL Server允許在不同的架構中有相同的表名字，因此當只有表名稱的時候會有混淆。所以我認為儘管放心使用就好了。

相容性視圖

相容性視圖是維護元數據的視圖，在SQL Server 2005之前是有系統表支持的，並且只向後相容。只在2005之後的版本支持對於某些系統表的查詢，例如分區表等，只有部分元數據或者特性是對用戶可見的。對於帶有很多用戶、群組、角色或者2000版本數據類型的資料庫而言，使用相容性視圖是有潛在風險的，因為視圖中有的列存儲了用戶的ID或者類型ID，可能會返回NULL或者觸發溢出。

目錄視圖

目錄視圖提供了關於資料庫架構的信息。它們也被資料庫引擎自己本身使用，尤其在查詢優化環節。因此這些視圖需要更高效的方式來獲取元數據。除了複製、備份、資料庫維護計劃或SQL Server代理目錄數據之外，所有元數據都通過這些編目視圖公開。

這些視圖用一種相當特殊的方式排列，SQL Server對象的共有信息都保存在sys.objects裡面。有許多派生視圖，比如外鍵、約束、服務隊列、表、視圖和過程，這些視圖用特定於被編目的對象類型的信息來補充一般的對象信息

並非SQL Server元數據中的所有內容都是對象。例如，一個列、索引或分佈統計信息不是對象。一些如主鍵約束或擴展屬性有一個奇怪的兩面性，因為它們被被當做為一個對象，當被強制鍵索引的實例化時，它就不是一個對象。有些對象(主要是約束)與另一種類型的對象具有父/子關係;父即表。

數據層應用程式視圖

數據層應用程式視圖被用於訪問註冊伺服器信息。特殊版本的伺服器和信息用來檢查這些版本是否漂移。這是一種作為容易的檢查當前註冊資料庫版本的方式，直接用T-SQL查詢。

動態管理視圖和功能(DMVs)

DMV一般用來調優，診斷問題和監控資料庫伺服器狀態。最重要的作用就是提供了一種方式來查詢資料庫的使用信息。例如，不僅查詢到索引，而且可以查詢到使用量的排序和耗時等。

元數據function

還有很多元數據函數，如object_name()或col_name()，它們提供關於當前資料庫中的模式作用域對象的信息。通過避免在元數據表達式中進行顯式連接，它們提供了獲取信息的捷徑，因此，當與編目視圖一起使用時，它們可以幫助您更快地獲取關於元數據的信息。

目錄存儲過程

有許多存儲過程的主要功能是為SQL Server的ODBC驅動程式提供元數據信息。當您建立ODBC連接時，該信息作為數據對象的集合。但是，這些信息通常是可用的，並且可以像任何其他存儲過程一樣從SQL中使用。它們通常被認為不如目錄視圖有用，因為存儲過程返回的結果必須使用INSERT插入一個表或者表變數中，需要使用INSERT ... EXECUTE 語法。

為什麼元數據視圖和功能很重要?

元數據視圖和函數允許您搜索元數據,提供對資料庫報告和總結,找出誰有許可權查看或改變什麼數據,讓你減少重覆輸入,讓幾乎所有隱藏在SQL Server Management Studio的信息可查詢,使部署腳本更安全,更可靠,找出最近的改變或創建,快速處理一些函數或過程,確定已註冊資料庫的版本，審計用於編碼實踐的資料庫代碼，發現重覆索引並且允許減少低效的點擊操作。當與其他SQL Server工具(如預設跟蹤和動態管理對象)結合使用時，使用強大的SQL腳本用於開發和管理資料庫是相當快速的。

元數據視圖和函數允許執行幾乎不可能執行的操作，例如查找依賴於指定的CLR用戶定義類型或別名類型的參數。

我是如何逐漸使用的?

學習使用元數據視圖和函數的第一階段是收集從各種著名的數據源(如SQL Server Central)中使用它們的查詢。可以在MSDN上查詢到。使用記錄工具保存這些查詢。如果它是一個用來保存註釋或片段的工具，可以讓您在任何地方輕鬆地獲取查詢，那麼它將會有所幫助。一段時間後，就可以根據使用需要對這些查詢稍作修改。然後，不需要在object browser窗格中搜索表列表，您很快就可以從集合中獲取適當的查詢，執行它，並快速獲取信息。

比較有用的查詢實例

下麵我會展示的例子都已經在2008和2012 兩個版本中測試。當然只用到了各自版本的最後一個版本更新後的資料庫。

下圖中展示了所有繼承sys.objects列的視圖。這意味著它們除了擁有這些列以外，還有列的對應類型。這是視圖所有的信息比如create_date也都來自sys.objects。

要列出資料庫中的所有視圖(存儲過程和外鍵)，只需執行以下操作 …

SELECT  object_schema_name(object_id)+'.'+name FROM sys.views;

 SELECT  object_schema_name(object_id)+'.'+name FROM sys.procedures;

SELECT name AS Foreign_key,object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent,

object_schema_name(referenced_object_ID)+'.'+object_name(referenced_object_ID) AS referenced

FROM sys.foreign_keys;

對於所有其他的，您需要使用一個系統函數來過濾您想要的對象。下麵的代碼提供了一些有用的示例。因為我們只獲取對象的名稱，所以使用sys.objects，它具有所有資料庫對象共有的基本信息的視圖。如果我們需要特定於特定類型對象的信息，比如主鍵是否具有系統生成的名稱，那麼您就必須為該特定類型的對象使用視圖。

/* The Tables */
  --資料庫中的所有用戶表
    SELECT
      ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
    FROM sys.objects AS ob
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = ob.object_id
           AND ep.class = 1
           AND ep.minor_id = 0
    WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1 

/* The Views */
  --視圖
  SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
  FROM sys.objects ob  LEFT OUTER JOIN sys.extended_properties AS ep
      ON ep.major_id = ob.object_id
         AND ep.class = 1
         AND ep.minor_id = 0
  WHERE objectproperty(ob.object_id,'IsView')= 1 


/* The Check Constraints */
 --Check約束
    SELECT
      objects.name AS Name_of_Check_Constraint,
      Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
	  Coalesce(ep.value,'') AS documentation
   FROM sys.objects
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = objects.object_id AND ep.class=1
  	    AND ep.name='MS_Description'--microsoft 公約
    WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1 

/* The Constraints */ 

SELECT
--約束
  objects.name AS Name_of_Constraint, --see all constraints and parent table
  Lower(Replace(type_desc,'_',' ')),--the type of constraint
  Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;
 
/* The Defaults */
--預設
SELECT
  objects.name, 
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;
 
/* The Default Constraints */
--資料庫及其父表中的所有預設約束
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
	  Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id)+'.'+object_name(objects.parent_object_id) AS parent,
	  Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects 
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
  LEFT OUTER JOIN sys.extended_properties AS EP_parent
    ON ep.major_id = objects.parent_object_id
       AND ep.name = 'MS_Description' --the microsoft convention	  
 WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;
 
/* The Executables */
--資料庫中的所有可執行文件(過程、函數等)
SELECT
  oe.name AS Name_Of_Executable,
  Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oe.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;


/* The Extended Stored Procedures */ 
--資料庫中的所有擴展存儲過程
SELECT
  oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oep.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;
 
/* The Inline Functions */ 
--資料庫中的所有內聯函數
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ilf.object_id
       AND EP.name = 'MS_Description'
 WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;
 
/* The Primary Keys */ 
--資料庫中的所有主鍵及其父表
SELECT
  pk.name AS Primary_key,
  Object_Schema_Name(pk.parent_object_id) + '.' + Object_Name(pk.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = pk.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = pk.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;

/* The Stored Procedures */
--資料庫中的所有存儲過程
SELECT
  sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sp.object_id
       AND EP.minor_id = 0
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;
 
/* The Queues */ 
--資料庫中的所有隊列
SELECT
  q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = q.object_id
       AND EP.name = 'MS_Description'   
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;
 
/* The Rules */ 
--資料庫中的所有舊式規則
SELECT
  ru.name AS RuleName, --old-fashioned sybase-style rule
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ru.object_id
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;
 
/* The Scalar Functions */ 
--資料庫中的所有標量函數。
SELECT
  sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sf.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;

/* The System Tables */ 
--據庫中的所有系統表
SELECT
  st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = st.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;

--資料庫中的所有表，包括系統表
SELECT
  at.name AS TableName, 
  Lower(Replace(type_desc,'_',' ')),--約束的類型
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = at.object_id
       AND EP.name = 'MS_Description'
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;
 
/* The TVFs*/ 
--資料庫中的所有表值函數
SELECT
  tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tvf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;
 
--資料庫及其所有觸發器。
SELECT
  tr.name AS TriggerName,
  Object_Schema_Name(tr.parent_object_id) + '.' + Object_Name(tr.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tr.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = tr.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;
 
/* The Unique Constraints */ 
--資料庫及其父表中的所有惟一約束
SELECT uc.name AS Unique_constraint,--所有唯一的約束
object_schema_name(uc.parent_object_id)+'.'+object_name(uc.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = uc.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = uc.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;

當然我們也可以調整這些語句來方便我們的精確查找，比如:

--資料庫中的所有視圖在過去兩周內被修改的有：

SELECT name AS ViewName, convert(char(11),modify_date,113)

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsView')= 1

AND modify_date > dateadd(week,-2, GetDate());

--上個月創建的所有對象的名稱和類型

SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' ')))

FROM sys.objects obj

WHERE create_date > dateadd(month,-1, GetDate());

--DBO架構中所有基本對象的名稱和類型

SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' '))) as ObjectType

FROM sys.objects obj

WHERE parent_object_ID=0

AND schema_ID = schema_ID('dbo'); 

總結

　　如上，到這級別簡單實用足夠了。們已經介紹了一般的理論，並介紹了查找資料庫中的內容的基本方法。在下一篇中我將會深入介紹觸發器並且找到有效信息的排序以便於可以通過系統視圖從動態線上目錄中收集的有用信息。