自己製作線上查詢SQL Schema的功能

2009-04-22

30897
0
DataBase
2010-02-20

在團隊開發時，常常會遇到的困難就是資料庫的Table欄位說明不好維護，
不僅版本不一，有時候資料更新很慢，手上的文件資料永遠不會是最正確的版本...
因為大家寫程式都沒空了，哪有時間維護這些文件...簡單的說就是件煩人的差事...

在團隊開發時，常常會遇到的困難就是資料庫的Table欄位說明不好維護，
不僅版本不一，有時候資料更新很慢，手上的文件資料永遠不會是最正確的版本...
因為大家寫程式都沒空了，哪有時間維護這些文件...簡單的說就是件煩人的差事...

就算真的大家有空可以維護SQL文件了，卻還是要透過word文件分享，或是同事各自印各自的"武功祕笈"流傳...
對於持續在開發專案的團隊來說，這樣資料的分享方式，真的是太慢了，
而且重點是大家永遠都拼不出最完整的版本~哈

那為什麼不乾脆把Table的明細說明就直接寫在資料庫上呢？備份一起備份，新增一起新增，那該多好？

如果還能透過web查詢，把這資料直接秀在網頁上，或是產生成PDF檔，那豈不樂哉？

基於有了這樣的懶惰想法~所以前陣子就索性研究起來，然後才有了今天這個分享，希望有興趣的朋友可以試試看。

或是分享更簡單方便的作法:)

目前我們自己撰寫的查詢介面如下，可以查詢到table跟view的資料，另外也可以產生PDF檔列印，
讓有需要的同事可以隨時上網查詢及列印。

這篇文章的重點有兩個：

1. 利用工具協助維護SQL的資料。
2. 如何透過SQL query 來獲取資料庫Table欄位明細。

1. 利用工具協助維護SQL的資料。

在同事 kiwi 的介紹下，得知了在codeplex有套好用的SQL工具 Data Dictionary Creator ，
它可以協助你修改SQL的Table資料。

大家可以去 http://datadictionary.codeplex.com/ 下載。

它是用C#寫的，有source code，支援SQL2005，你可以自行中文化，看起來會比較順眼些 : )
小弟只是稍微修改了幾個地方，主要是希望看起來直覺些罷了。

首先要先設定與資料庫的連線，他有wizard，很方便！

接下來設定擴充屬性，這也是這軟體強的地方，讓你自定義擴充屬性，讓你的資料維護的更詳細，
在這裡我定義了reference及memo兩個欄位，作為參考table及備註。
切換到維護的畫面，因為已經跟SQL連線，所以當你選擇Table時，會自動將schema帶出，
再加上剛剛設定的兩個擴充屬性的欄位，也已經自動幫我們加好了，真的好方便啊！

剩下的，你只需要將資料補一補，打完資料後，只要修改的那欄 LostFocus，它就會回寫資料到
SQL Server，所以並無儲存鈕唷！
進到SQL Server，可以看到Table_1的MS_Description屬性已經更改了。

而欄位的部份也已經更改了。

欄位的擴充屬性也填上了我們自建的reference及memo說明。

到這邊算是成功了一半^_^

2. 如何透過SQL query 來查詢資料庫的說明。

SQL2005與SQL2000語法有些不同，我建議使用下列的語法建立一個view，供後續程式設計，
而語法說明我就不再解釋了，因為聰明如大家，從下列語法就可以知道哪些是關鍵的Table，
然後對照搜尋出的資料，我想應該蠻好理解的^_^

另外裡面因為有我自定義的兩個擴充欄位reference及memo，所以如果要直接使用的話，請自行修正，不然會找不到資料。

SQL2005語法

SELECT     TOP (100) PERCENT a.TABLE_CATALOG, a.TABLE_NAME, p.PK, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH,a.IS_NULLABLE, a.COLUMN_DEFAULT, c_1.value AS MS_Description, X.value AS memo, Y.value AS Table_Description, w.value AS refernece, q.type
FROM    INFORMATION_SCHEMA.COLUMNS AS a LEFT OUTER JOIN
        (SELECT     o.name AS TableName, c.name AS ColumnName, ep.value
        FROM          sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o
        INNER JOIN sys.extended_properties AS ep ON o.object_id = ep.major_id
        ON c.colid = ep.minor_id AND c.id = ep.major_id
        WHERE  ep.name = N'MS_Description') AS c_1 ON a.COLUMN_NAME = c_1.ColumnName
        AND a.TABLE_NAME = c_1.TableName

       LEFT OUTER JOIN
      (SELECT o.name AS TableName, c.name AS ColumnName, ep.value
       FROM  sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o INNER JOIN
       sys.extended_properties AS ep ON o.object_id = ep.major_id ON c.colid = ep.minor_id
       AND c.id = ep.major_id WHERE (ep.name = N'reference')) AS w
       ON a.COLUMN_NAME = w.ColumnName AND a.TABLE_NAME = w.TableName

       LEFT OUTER JOIN
       (SELECT     o.name AS TableName, c.name AS ColumnName, ep.value
        FROM sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o INNER JOIN
        sys.extended_properties AS ep ON o.object_id = ep.major_id ON c.colid = ep.minor_id
        AND c.id = ep.major_id
        WHERE      (ep.name = N'memo')) AS X ON a.COLUMN_NAME = X.ColumnName
        AND a.TABLE_NAME = X.TableName

       LEFT OUTER JOIN
       (SELECT     o.name AS TableName, c.name AS ColumnName, ep.value
       FROM sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o INNER JOIN
       sys.extended_properties AS ep ON o.object_id = ep.major_id ON c.colid = ep.minor_id
       AND c.id = ep.major_id
       WHERE  (ep.name = N'MS_Description') AND (c.name IS NULL)) AS Y
       ON a.TABLE_NAME = Y.TableName

       LEFT OUTER JOIN
       (SELECT     d.name AS tb, c.name AS col, 'PK' AS PK
       FROM  sys.sysindexes AS a INNER JOIN sys.sysindexkeys AS b ON a.indid = b.indid
      AND a.id = b.id INNER JOIN sys.syscolumns AS c ON a.id = c.id AND b.colid = c.colid
       INNER JOIN sys.sysobjects AS d ON c.id = d.id
       WHERE      (a.name LIKE N'pk%')) AS p ON a.TABLE_NAME = p.tb
       AND a.COLUMN_NAME = p.col LEFT OUTER JOIN sys.objects AS q
       ON a.TABLE_NAME = q.name
       ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION

SQL2000語法

SELECT     TOP 100 PERCENT a.TABLE_CATALOG, a.TABLE_NAME, p.PK, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH, a.IS_NULLABLE, a.COLUMN_DEFAULT, c_1.value AS MS_Description, X.value AS memo, Y.value AS Table_Description, w.value AS refernece, q.type
FROM         INFORMATION_SCHEMA.COLUMNS AS a LEFT OUTER JOIN
        (SELECT     o.name AS TableName, c.name AS ColumnName, ep.value
        FROM    dbo.syscolumns AS c RIGHT OUTER JOIN dbo.sysobjects AS o INNER JOIN
                   dbo.sysproperties AS ep ON o.id = ep.id ON c.id = ep.id AND c.colid = ep.smallid
        WHERE      (ep.name = N'MS_Description')) AS c_1 ON a.COLUMN_NAME = c_1.ColumnName
        AND  a.TABLE_NAME = c_1.TableName

        LEFT OUTER JOIN
        (SELECT     o.name AS TableName, c.name AS ColumnName, ep.value
         FROM   dbo.syscolumns AS c RIGHT OUTER JOIN dbo.sysobjects AS o INNER JOIN
                    dbo.sysproperties AS ep ON o.id = ep.id ON c.colid = ep.id AND c.id = ep.smallid
         WHERE      (ep.name = N'reference')) AS w ON a.COLUMN_NAME = w.ColumnName
         AND a.TABLE_NAME = w.TableName

         LEFT OUTER JOIN
         (SELECT     o.name AS TableName, c.name AS ColumnName, ep.value
          FROM          dbo.syscolumns AS c RIGHT OUTER JOIN dbo.sysobjects AS o INNER JOIN
          dbo.sysproperties AS ep ON o.id = ep.id ON c.colid = ep.id AND c.id = ep.smallid
          WHERE      (ep.name = N'memo')) AS X ON a.COLUMN_NAME = X.ColumnName
          AND a.TABLE_NAME = X.TableName

          LEFT OUTER JOIN
          (SELECT o.name AS TableName, c.name AS ColumnName, ep.value
           FROM    dbo.syscolumns AS c RIGHT OUTER JOIN dbo.sysobjects AS o INNER JOIN
           dbo.sysproperties AS ep ON o.id = ep.id ON c.id = ep.id AND c.colid = ep.smallid
           WHERE      (ep.name = N'MS_Description') AND (c.name IS NULL)) AS Y O
           N a.TABLE_NAME = Y.TableName

          LEFT OUTER JOIN
          (SELECT     d.name AS tb, c.name AS col, 'PK' AS PK
           FROM          dbo.sysindexes AS a INNER JOIN
                    dbo.sysindexkeys AS b ON a.indid = b.indid AND a.id = b.id INNER JOIN
                     dbo.syscolumns AS c ON a.id = c.id AND b.colid = c.colid INNER JOIN
                    dbo.sysobjects AS d ON c.id = d.id
           WHERE      (a.name LIKE N'pk%')) AS p ON a.TABLE_NAME = p.tb
           AND a.COLUMN_NAME = p.col

          LEFT OUTER JOIN
          dbo.sysobjects AS q ON a.TABLE_NAME = q.name
          ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION

最後稍微撰寫一下屬於你自己的查詢程式，就可以有很好的效果了。

最後一張圖是我們產生的PDF檔畫面，是不是很方便阿？隨時隨地都有最新的資料！

回首頁