解析SQL 表結構信息查詢含主外鍵、自增長

2024-07-24 12:48:21

字體：大中小

來源：轉載

供稿：網友

最近項目需要做什么數據字典，需要表結構信息。在網上看了許多關于表結構信息的查詢，感覺都不怎么樣。相對好一點就是《基于SQL2005 SQL2008 表結構信息查詢升級版的詳解(含外鍵信息)》，但是這里有一點小問題，缺少一個過濾以致運行有一點小bug。在AdventureWorks2012數據庫中的Address表查詢結果如圖：

在查詢過濾中我們添加以下信息就ok了：
AND g.class_desc = 'OBJECT_OR_COLUMN'
修改后的SQL如下：
復制代碼代碼如下:

SELECT 表名 = CASE WHEN a.colorder = 1 THEN d.name
                  ELSE ''
             END ,
        表說明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')
                   ELSE ''
              END ,
        字段序號 = a.colorder ,
        字段名 = a.name ,
        標識 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
                  ELSE ''
             END ,
        主鍵 = CASE WHEN EXISTS ( SELECT 1
                                FROM    dbo.sysindexes si
                                        INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                              AND si.indid = sik.indid
                                        INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                              AND sc.colid = sik.colid
                                        INNER JOIN dbo.sysobjects so ON so.name = so.name
                                                              AND so.xtype = 'PK'
                                WHERE   sc.id = a.id
                                        AND sc.colid = a.colid ) THEN '√'
                  ELSE ''
             END ,
        外鍵 = CASE WHEN tony.fkey IS NOT NULL
                       AND tony.fkey = a.colid THEN '√'
                  ELSE ''
             END ,
        外鍵表 = CASE WHEN tony.fkey IS NOT NULL
                        AND tony.fkey = a.colid THEN OBJECT_NAME(tony.fkeyid)
                   ELSE ''
              END ,
        外鍵字段 = CASE WHEN tony.fkey IS NOT NULL
                         AND tony.fkey = a.colid
                    THEN ( SELECT   name
                           FROM     syscolumns
                           WHERE    colid = tony.fkey
                                    AND id = tony.fkeyid
                         )
                    ELSE ''
               END ,
        類型 = b.name ,
        長度 = a.length ,
        精度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,
        小數位數 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
        允許空 = CASE WHEN a.isnullable = 1 THEN '√'
                   ELSE ''
              END ,
        默認值 = ISNULL(e.text, '') ,
        字段說明 = ISNULL(g.[value], '') ,
        創建時間 = d.crdate ,
        更改時間 = CASE WHEN a.colorder = 1 THEN d.refdate
                    ELSE NULL
               END
FROM    dbo.syscolumns a
        LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype
        INNER JOIN dbo.sysobjects d ON a.id = d.id
                                       AND d.xtype = 'U'
                                       AND d.status >= 0
        LEFT JOIN dbo.syscomments e ON a.cdefault = e.id
        LEFT JOIN sys.extended_properties g ON a.id = g.major_id
                                               AND a.colid = g.minor_id
                                               AND g.class_desc = 'OBJECT_OR_COLUMN'
        LEFT JOIN sys.extended_properties f ON d.id = f.major_id
                                               AND f.minor_id = 0
        LEFT JOIN sysobjects htl ON htl.parent_obj = d.id
                                    AND htl.xtype = 'F'
        LEFT JOIN sysforeignkeys tony ON htl.id = tony.constid
WHERE   d.name = 'Address' --這里輸入包含表名稱的條件
ORDER BY d.id ,
        a.colorder

上一篇：解析mysql不重復字段值求和

下一篇：MySql實現跨表查詢的方法詳解

發表評論

共有條評論