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

創(chuàng)建能夠保持最新數(shù)據(jù)字典的方法。對(duì)數(shù)據(jù)庫做了很多更改，而花費(fèi)于數(shù)據(jù)庫文檔更新的時(shí)間多于數(shù)據(jù)庫管理的時(shí)間。
    examda提示: 如果你將元數(shù)據(jù)存儲(chǔ)為擴(kuò)展屬性，那么你可以使用SQL Server 2005在幾秒之內(nèi)為一個(gè)數(shù)據(jù)庫創(chuàng)建一個(gè)數(shù)據(jù)字典。SQL Server 2005 AdventureWorks示例數(shù)據(jù)庫包含了眾多擴(kuò)展屬性，所以這個(gè)數(shù)據(jù)庫是一個(gè)很好的示例。在這篇文章里，我們將介紹兩個(gè)核心內(nèi)容。首先是一組腳本示例，它為表和字段添加擴(kuò)展屬性。其次是生成HTML格式數(shù)據(jù)字典的T-SQL代碼。
    示例腳本——sys.sp_addextendedproperty
    下面是一個(gè)示例腳本，它添加擴(kuò)展屬性到這個(gè)數(shù)據(jù)庫上。
    為表和字段添加擴(kuò)展屬性
    /**********　　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中的對(duì)象并選擇屬性來查看擴(kuò)展屬性：
    如果你的數(shù)據(jù)庫在擴(kuò)展屬性中有數(shù)據(jù)，那么你可以運(yùn)行查詢來提取這個(gè)數(shù)據(jù)。在SQL Server管理套件中，選擇Tools | Options，并在Results to Text中不選“Include column headers in the result set”(在結(jié)果集中包含字段頭)選項(xiàng)。這將使顯示在每個(gè)字段名稱下面的結(jié)果集都不包含字段頭。
    HTML格式的數(shù)據(jù)字典
    運(yùn)行下面的腳本來生成數(shù)據(jù)字典，并保存結(jié)果到一個(gè)擴(kuò)展名為“.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
    這個(gè)腳本將被安排作為一個(gè)工作來運(yùn)行，所以你不需要擔(dān)心要手動(dòng)更新文檔。
    因?yàn)槟銓⑽臋n存儲(chǔ)在數(shù)據(jù)庫中，所以你不必?fù)?dān)心擁有多個(gè)拷貝并指出哪個(gè)是最新的。它還會(huì)與數(shù)據(jù)庫一起備份。
    這個(gè)腳本不會(huì)添加擴(kuò)展屬性，但是顯然你將丟失這些描述，所以花些時(shí)間將這些信息添加到你的環(huán)境中。