甩掉資料字典，讓Sql Server資料庫也來一個自描述

我們學、用.NET的都知道，程式集(Assembly)的一個很大的優點就是它有中繼資料，可以“自描述”。在我們體驗這種優勢的同時，我們是否想過什麼時候資料庫中的資訊也是自描述的該多好？

我想Sql Server中有擴充屬性，並且Sql Server的“中繼資料”，其實都可以通過一些系統資料表、系統檢視表等中查到，所以我想為何不利用一下這些擴充屬性，讓資料庫中的表、視圖、欄位等也來一個自我描述，省去手工維護那些Excel格式之類的外部資料字典的麻煩？

以下是我的初步實踐，可能並不完美，希望可以給大家一個拋磚引玉的效果就好。

首先，對於資料表中欄位，因為在你用SSMS給一個表添加欄位的時候，就有一個屬性可以填寫一個描述，因此擷取這些描述資訊自然是非常簡單的，我實現了如下視圖：

CREATE VIEW [dbo].[DOC_vwColumns]
AS
-- 資料表欄位的說明
-- 作者：RickyLin, http://Ricky81317.cnblogs.com/
SELECT T.Table_Name, C.Column_Name, C.Data_Type, C.Character_Maximum_Length, C.Is_Nullable, P.[Value] AS ColumnDescription
FROM INFORMATION_SCHEMA.Tables T
INNER JOIN INFORMATION_SCHEMA.Columns C ON C.Table_Name = T.Table_Name
LEFT JOIN Sys.Extended_Properties P ON P.Major_ID = Object_ID(T.Table_Name) AND P.Minor_ID = C.Ordinal_Position AND P.[Name] = 'MS_Description'
WHERE T.Table_Type = 'BASE TABLE'
--ORDER BY T.Table_Name, C.Ordinal_Position

 

通過查詢這個視圖，就可以得到各個資料表中的欄位的說明，而且因為是一個視圖，所以可以通過添加Where條件，從這個視圖中篩選出你需要的個別表的欄位資訊

 

對於資料庫中的表、視圖、預存程序、函數等，在你建立的時候，並沒有一個明顯的可以填寫“描述”的地方，但是，當你建立這些資料庫物件完成之後，可以現在SSMS中選中這個對象，然後右鍵-->屬性，就可以在屬性對話方塊的左邊的找到“擴充屬性”，然後在擴充屬性中增加一條，用來描述對象的資訊。我是使用ObjectDescription這個名字添加的擴充屬性，添加完成之後，點擊確定。然後可以利用以下視圖進行查詢：

CREATE VIEW [dbo].[DOC_vwObjects]
AS
-- 資料庫物件的說明
-- 作者：RickyLin, http://Ricky81317.cnblogs.com/
SELECT T.[Object_ID] AS ObjectID, T.[Name] AS ObjectName, T.[Type] AS ObjectType, P.[Value] AS ObjectDescription
FROM Sys.Objects T
LEFT JOIN Sys.extended_properties P ON P.Major_ID = T.[Object_ID]
WHERE P.[Name] = 'ObjectDescription'

是不是很簡單？

通過這種方法，可以為我們的資料庫建立起一個自描述的方式，可以用比較方便的方法，維護和查詢說明資訊，並且在無論我們備份還是分離資料庫，這些描述資訊都是在資料庫裡面隨著資料庫檔案走（這也是自描述的優勢啊），不需要額外的附帶著其他檔案性質說明文檔。就算要一份最新的Excel格式的資料庫字典，也只需要查詢出結果，然後複製、粘貼到Excel裡面就行了。