使用擴展屬性快速創(chuàng)建SQLServer數(shù)據(jù)字典

創(chuàng)建能夠保持最新數(shù)據(jù)字典的方法。對數(shù)據(jù)庫做了很多更改，而花費于數(shù)據(jù)庫文檔更新的時間多于數(shù)據(jù)庫管理的時間。
    examda提示: 如果你將元數(shù)據(jù)存儲為擴展屬性，那么你可以使用SQL Server 2005在幾秒之內(nèi)為一個數(shù)據(jù)庫創(chuàng)建一個數(shù)據(jù)字典。SQL Server 2005 AdventureWorks示例數(shù)據(jù)庫包含了眾多擴展屬性，所以這個數(shù)據(jù)庫是一個很好的示例。在這篇文章里，我們將介紹兩個核心內(nèi)容。首先是一組腳本示例，它為表和字段添加擴展屬性。其次是生成HTML格式數(shù)據(jù)字典的T-SQL代碼。
    示例腳本——sys.sp_addextendedproperty
    下面是一個示例腳本，它添加擴展屬性到這個數(shù)據(jù)庫上。
    為表和字段添加擴展屬性
    /**********　　The following extended properties already exist in the AdventureWorks database. There is no need to run the script against the database in order for the remaining samples to work.
    **********/
    USE [AdventureWorks]
    GO
    --Script to add an Extended Property to the Table
    EXEC sys.sp_addextendedproperty
    @name=N’MS_Description’,
    @value=N’Street address information for customers, employees, and vendors.’ ,
    @level0type=N’SCHEMA’,
    @level0name=N’Person’, --Schema Name
    @level1type=N’TABLE’,
    @level1name=N’Address’ --Table Name
    GO
    --Script to add an Extended Property to a column
    EXEC sys.sp_addextendedproperty
    @name=N’MS_Description’,
    @value=N’First street address line.’ ,
    @level0type=N’SCHEMA’,
    @level0name=N’Person’, --Schema Name
    @level1type=N’TABLE’,
    @level1name=N’Address’,--Table Name
    @level2type=N’COLUMN’,
    @level2name=N’AddressLine1’--Column Name
    Go可以通過右鍵單擊SSMS中的對象并選擇屬性來查看擴展屬性：
    如果你的數(shù)據(jù)庫在擴展屬性中有數(shù)據(jù)，那么你可以運行查詢來提取這個數(shù)據(jù)。在SQL Server管理套件中，選擇Tools | Options，并在Results to Text中不選“Include column headers in the result set”(在結(jié)果集中包含字段頭)選項。這將使顯示在每個字段名稱下面的結(jié)果集都不包含字段頭。
    HTML格式的數(shù)據(jù)字典
    運行下面的腳本來生成數(shù)據(jù)字典，并保存結(jié)果到一個擴展名為“.htm”的文件中。
    生成數(shù)據(jù)字典的示例T-SQL腳本
    Set nocount on
    DECLARE @TableName nvarchar(35)
    DECLARE Tbls CURSOR
    FOR
    Select distinct Table_name
    FROM INFORMATION_SCHEMA.COLUMNS
    --put any exclusions here
    --where table_name not like ’%old’
    order by Table_name
    OPEN Tbls
    PRINT ’’
    FETCH NEXT FROM Tbls
    INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT ’
’
    PRINT ’’
    Print ’’ + @TableName + ’’
    PRINT ’
’
    --Get the Description of the table
    --Characters 1-250
    Select substring(cast(Value as varchar(1000)),1,250) FROM
    sys.extended_properties A
    WHERE A.major_id = OBJECT_ID(@TableName)
    and name = ’MS_Description’ and minor_id = 0
    --Characters 251-500
    Select substring(cast(Value as varchar(1000)),251, 250) FROM
    sys.extended_properties A
    WHERE A.major_id = OBJECT_ID(@TableName)
    and name = ’MS_Description’ and minor_id = 0
    PRINT ’’--Set up the Column Headers for the Table
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    PRINT ’’
    --Get the Table Data
    SELECT ’’,
    ’’,
    ’’,
    ’’,
    ’’,
    ’’,
    ’’ ,
    ’’,
    ’’,
    ’’,
    ’’ ,
    ’’ ,
    ’’ ,
    ’’
    FROM sys.tables AS tblINNER JOIN sys.all_columns AS clmns
    ON clmns.object_id=tbl.object_id
    LEFT OUTER JOIN sys.indexes AS idx
    ON idx.object_id = clmns.object_id
    AND 1 =idx.is_primary_key
    LEFT OUTER JOIN sys.index_columns AS idxcol
    ON idxcol.index_id = idx.index_id
    AND idxcol.column_id = clmns.column_id
    AND idxcol.object_id = clmns.object_id
    AND 0 = idxcol.is_included_column
    LEFT OUTER JOIN sys.types AS udt
    ON udt.user_type_id = clmns.user_type_id
    LEFT OUTER JOIN sys.types AS typ
    ON typ.user_type_id = clmns.system_type_id
    AND typ.user_type_id = typ.system_type_id
    LEFT JOIN sys.default_constraints AS cnstr
    ON cnstr.object_id=clmns.default_object_id
    LEFT OUTER JOIN sys.extended_properties exprop
    ON exprop.major_id = clmns.object_id
    AND exprop.minor_id = clmns.column_id
    AND exprop.name = ’MS_Description’
    WHERE (tbl.name = @TableName and
    exprop.class = 1) --I don’t wand to include comments on indexes
    ORDER BY clmns.column_id ASC
    PRINT ’

Column Name	Description	InPrimaryKey	IsForeignKey	DataType	Length	Numeric Precision	Numeric Scale	Nullable	Computed	Identity	Default Value
’ + CAST(clmns.name AS VARCHAR(35)) + ’	’ + substring(ISNULL(CAST(exprop.value AS VARCHAR(255)),’’),1,250),     substring(ISNULL(CAST(exprop.value AS VARCHAR(500)),’’),251,250) + ’	’ + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + ’	’ + CAST(ISNULL(     (SELECT 1 1     FROM sys.foreign_key_columns AS fkclmn     WHERE fkclmn.parent_column_id = clmns.column_id     AND fkclmn.parent_object_id = clmns.object_id     ), 0) AS VARCHAR(20)) + ’	’ + CAST(udt.name AS CHAR(15)) + ’	’ + CAST(CAST(CASE WHEN typ.name IN (N’nchar’, N’nvarchar’) AND clmns.max_length <> -1     THEN clmns.max_length/2     ELSE clmns.max_length END AS INT) AS VARCHAR(20)) + ’	’ + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + ’	’ + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + ’	’ + CAST(clmns.is_nullable AS VARCHAR(20)) + ’	’ + CAST(clmns.is_computed AS VARCHAR(20)) + ’	’ + CAST(clmns.is_identity AS VARCHAR(20)) + ’	’ + isnull(CAST(cnstr.definition AS VARCHAR(20)),’’) + ’

’
    FETCH NEXT FROM Tbls
    INTO @TableName
    END
    PRINT ’’
    CLOSE Tbls
    DEALLOCATE Tbls
    這個腳本將被安排作為一個工作來運行，所以你不需要擔(dān)心要手動更新文檔。
    因為你將文檔存儲在數(shù)據(jù)庫中，所以你不必擔(dān)心擁有多個拷貝并指出哪個是最新的。它還會與數(shù)據(jù)庫一起備份。
    這個腳本不會添加擴展屬性，但是顯然你將丟失這些描述，所以花些時間將這些信息添加到你的環(huán)境中。