ASP.NET Web開發架構之六 資料庫文檔方法，工具和實踐

文章目錄

• SQL Doc
• Management Table Description
• Custom Table 自訂表格

隨著項目的進度推進，資料庫的表越來越多，欄位也頻繁的變更。如何對這些變更作出文檔說明，既要方便，又要有可維護性。比如，可以為所有的表建立WORD文檔來說明，各個表的含義和欄位的意義，但是如果資料庫表或欄位變更頻繁的話，更新WORD文檔是個相對麻煩的工作。在部落格園中曾經有技術人員專門建立文檔產生工具，以程式的方式直接產生WORD文檔，使為資料庫建立文檔的可行性提高很多，輕鬆點擊滑鼠就可以做完的工作，非常容易。

SQL Doc

推薦給各位的資料庫文檔工具箱中，排名第一的是RedGate公司的SQL Doc。

建立新項目，串連資料庫伺服器，選擇資料庫，在右邊的窗格中為表或它的欄位建立描述資訊。

使用SQL Profiler查詢跟蹤器，檢測到它進行如下的SQL語句

exec sp_executesql N'if (SELECT count(*) FROM ::fn_listextendedproperty (@propertyName, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name))> 0 begin     EXEC sp_updateextendedproperty  @propertyName, @propertyValue, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name; end else begin     EXEC sp_addextendedproperty @propertyName, @propertyValue, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name; end',N'@propertyName nvarchar(14),@myLevel0Type nvarchar(6),@myLevel0Name nvarchar(3),@myLevel1Type nvarchar(5),@myLevel1Name nvarchar(10),@myLevel2Type nvarchar(6),@myLevel2Name nvarchar(6),@propertyValue nvarchar(4)',@propertyName=N'MS_Description',@myLevel0Type=N'SCHEMA',@myLevel0Name=N'dbo',@myLevel1Type=N'TABLE',@myLevel1Name=N'ADBBSYSMSG',@myLevel2Type=N'COLUMN',@myLevel2Name=N'RECNUM',@propertyValue=N'記錄編號'

它使用附加屬性的方式，為表或欄位建立備忘資訊，直接存放在資料庫中。

回到SQL Server Management Studio中查看，看到它直接附加在欄位的擴充屬性中。

方便的地方不僅僅在這裡，當你右鍵點擊表，以Script Table As產生表的建立指令碼時，它的最後一行，會添加屬性

CREATE TABLE [dbo].[ADBBSYSMSG](    [RECNUM] [int] IDENTITY(1,1) NOT NULL,    [SYSMSG_ID] [nvarchar](16) NOT NULL,    [MESSAGE] [nvarchar](255) NOT NULL,    [CREATED_DATE] [datetime] NOT NULL,    [CREATED_BY] [nvarchar](10) NOT NULL,    [REVISED_DATE] [datetime] NOT NULL,    [REVISED_BY] [nvarchar](10) NOT NULL, CONSTRAINT [PK_ADBBSYSMSG] PRIMARY KEY CLUSTERED (    [SYSMSG_ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]) ON [PRIMARY]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'記錄編號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ADBBSYSMSG', @level2type=N'COLUMN',@level2name=N'RECNUM'GO

到目前為止，這是我發現的最優秀的資料庫文檔工具。新的2.1版本支援SQL Server 2008 R2。

 

Management Table Description

在沒有找到SQL Doc之前，我一直尋找一種方法能為資料庫建立文檔，又便於維護。經過摸索，自己建立了如下工具，以維護資料庫文檔。

這個工具的原理，和上面的SQL Doc一樣，也是運用擴充屬性。關鍵區段的C#代碼如下所示

    string tablesql = @"  if(select count(1) from fn_listextendedproperty(N'MS_Description'," +   "   N'SCHEMA',N'{2}',N'TABLE',N'{0}',NULL,NULL))>0 " +  "    EXEC sp_updateextendedproperty N'MS_Description',N'{1}', " +  "    N'SCHEMA',N'{2}', N'TABLE',N'{0}',NULL,NULL  " +  "   ELSE   " + "   EXEC sp_addextendedproperty  N'MS_Description',N'{1}', " + "  N'SCHEMA',N'{2}',N'TABLE',N'{0}'   ";        //更新欄位的描述 {2} 預設是dbo,但是AdventureWorks出現大量不是dbo的情況        string fieldSql=@"if(select count(1) from fn_listextendedproperty(N'MS_Description',N'SCHEMA', "+       " N'{3}',N'TABLE',N'{0}',N'COLUMN', N'{1}'))>0   "+       " EXEC sp_updateextendedproperty N'MS_Description',N'{2}',  "+       "    N'SCHEMA',N'{3}', N'TABLE',N'{0}',N'COLUMN', N'{1}'  "+       "  ELSE   "+       "  EXEC sp_addextendedproperty  N'MS_Description',N'{2}', "+        "      N'SCHEMA',N'{3}',N'TABLE',N'{0}',N'COLUMN', N'{1}'   ";        //讀取表的描述        string getTableDescription = " select value from  " +        " fn_listextendedproperty(N'MS_Description',N'SCHEMA',N'{1}',N'TABLE'," +        " N'{0}',NULL, NULL)  ";        //讀取欄位的描述        string getFieldDescription=" select objname,value from  "+        " fn_listextendedproperty(N'MS_Description',N'SCHEMA',N'{1}',N'TABLE',"+        " N'{0}',N'COLUMN', NULL)  ";

tableSql是對錶進行注釋，添加擴充屬性，fieldSql是對欄位添加擴充屬性，下面的兩行分別用於讀取。整個工具，最關鍵的部分也是這裡，這些是我用查詢跟蹤器，跟蹤出來的。儘管可以直接用SQL Server Management Studio直接完成這個工作，但是沒有這個小工具直觀，把這些做成工具，方便維護。

 

Custom Table 自訂表格

這種方法比較保守，也很穩定。在資料庫中專門建立資料表，來存放表及其欄位的含義，再設計工具對此進行更新和維護。以金蝶系統為例子，它就是應用這個方法。

要查詢表及其欄位的文檔資訊，需要執行如下的語句

select * from t_tabledescription select * from t_fielddescription 

--要查具體的某張表描述，執行該語句： select * from t_fielddescription where FTableID=(select FTableID from t_tabledescription where FTableName='t_Item') 

此外，金碟的協助工具輔助，查看錶描述資訊，也做的簡單實用。

金碟的產品線眾多，不同產品資料表的用途會有差異，比如這裡的適用於服裝鞋帽版。

這種方法簡單直觀，直接在表中存放描述資訊。需要提供更新工具，讀取查看工具，如果能匯出成WORD/EXCEL那就更加完美了。