解析SQL 表結(jié)構(gòu)信息查詢 含主外鍵、自增長
更新時間：2013年06月21日 11:00:31 作者：
本篇文章是對SQL 表結(jié)構(gòu)信息查詢 含主外鍵、自增長進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下
最近項目需要做什么數(shù)據(jù)字典，需要表結(jié)構(gòu)信息。在網(wǎng)上看了許多關(guān)于表結(jié)構(gòu)信息的查詢，感覺都不怎么樣。相對好一點就是《基于SQL2005 SQL2008 表結(jié)構(gòu)信息查詢升級版的詳解(含外鍵信息)》 ，但是這里有一點小問題，缺少一個過濾以致運行有一點小bug。在AdventureWorks2012數(shù)據(jù)庫中的Address表查詢結(jié)果如圖：
在查詢過濾中我們添加以下信息就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 ,
標(biāo)識 = 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') ,
小數(shù)位數(shù) = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
允許空 = CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ,
默認(rèn)值 = ISNULL(e.text, '') ,
字段說明 = ISNULL(g.[value], '') ,
創(chuàng)建時間 = 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
運行結(jié)果如圖：
我不怎么喜歡它的“類型”信息，一般的varchar都會有長度信息，還有這個查詢對于SQL 2012的新數(shù)據(jù)類型不支持，該SQL里面的嵌套查詢比較多，于是我就自己重新寫了一個SQL。
這里提醒大家盡量用INFORMATION_SCHEMA.XXX視圖而不去用sys.XXX視圖。
新的SQL如下：
SELECT
--OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) AS [object_id] ,
CASE WHEN a.ORDINAL_POSITION = 1
THEN a.TABLE_SCHEMA + '.' + a.TABLE_NAME
ELSE ''
END AS TABLE_NAME ,
CASE WHEN ( a.ORDINAL_POSITION = 1
AND p1.value IS NOT NULL
) THEN p1.value
ELSE ''
END AS TABLE_Description ,
a.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX('char', a.DATA_TYPE) > 0
OR CHARINDEX('binary', a.DATA_TYPE) > 0
)
AND a.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN a.DATA_TYPE + '('
+ CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
WHEN ( ( CHARINDEX('CHAR', a.DATA_TYPE) > 0
OR CHARINDEX('binary', a.DATA_TYPE) > 0
)
AND a.CHARACTER_MAXIMUM_LENGTH = -1
) THEN a.DATA_TYPE + '(max)'
WHEN ( CHARINDEX('numeric', a.DATA_TYPE) > 0 )
THEN a.DATA_TYPE + '(' + CAST(a.NUMERIC_PRECISION AS VARCHAR(4))
+ ',' + CAST(a.NUMERIC_SCALE AS VARCHAR(4)) + ')'
ELSE a.DATA_TYPE
END AS COLUMN_TYPE ,
CASE WHEN c.IS_IDENTITY = 1 THEN 'YES'
ELSE 'NO'
END AS IS_IDENTITY ,
a.IS_NULLABLE ,
CASE WHEN a.COLUMN_DEFAULT IS NULL THEN ''
ELSE a.COLUMN_DEFAULT
END AS Default_Value ,
CASE WHEN p.value IS NULL THEN ''
ELSE p.value
END AS [COLUMN_Description] ,
CASE WHEN o.name IS NULL THEN ''
ELSE '√'
END AS Is_PrimaryKey ,
CASE WHEN f.parent_column_id IS NULL THEN ''
ELSE '√'
END AS Is_Foreignkeys ,
CASE WHEN referenced_object_id IS NULL THEN ''
ELSE OBJECT_NAME(referenced_object_id)
END AS Foreign_Table ,
CASE WHEN referenced_object_id IS NULL THEN ''
ELSE ( SELECT name
FROM sys.columns
WHERE object_id = f.referenced_object_id
AND column_id = f.referenced_column_id
)
END AS Foreign_key
FROM INFORMATION_SCHEMA.COLUMNS a
INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = c.OBJECT_ID
AND a.COLUMN_NAME = c.NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.COLUMN_NAME = b.COLUMN_NAME
LEFT JOIN sys.sysobjects o ON o.name = b.CONSTRAINT_NAME
AND o.xtype = 'PK'
LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = p.major_id
AND a.Ordinal_position = p.minor_id
AND p.class_desc = 'OBJECT_OR_COLUMN'
LEFT JOIN sys.extended_properties p1 ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = p1.major_id
AND p1.minor_id = 0
LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = f.parent_object_id
AND a.ORDINAL_POSITION = f.parent_column_id
WHERE a.TABLE_NAME = 'Address'
-- a.TABLE_NAME IN (SELECT name FROM sys.tables)
ORDER BY a.TABLE_SCHEMA,a.TABLE_NAME, a.ORDINAL_POSITION
運行效果如圖：
有不對的地方還請大家拍磚！謝謝！
在查詢過濾中我們添加以下信息就ok了：
AND g.class_desc = 'OBJECT_OR_COLUMN'
修改后的SQL如下：
復(fù)制代碼 代碼如下:
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 ,
標(biāo)識 = 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') ,
小數(shù)位數(shù) = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
允許空 = CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ,
默認(rèn)值 = ISNULL(e.text, '') ,
字段說明 = ISNULL(g.[value], '') ,
創(chuàng)建時間 = 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
運行結(jié)果如圖：
我不怎么喜歡它的“類型”信息，一般的varchar都會有長度信息，還有這個查詢對于SQL 2012的新數(shù)據(jù)類型不支持，該SQL里面的嵌套查詢比較多，于是我就自己重新寫了一個SQL。
這里提醒大家盡量用INFORMATION_SCHEMA.XXX視圖而不去用sys.XXX視圖。
新的SQL如下：
復(fù)制代碼 代碼如下:
SELECT
--OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) AS [object_id] ,
CASE WHEN a.ORDINAL_POSITION = 1
THEN a.TABLE_SCHEMA + '.' + a.TABLE_NAME
ELSE ''
END AS TABLE_NAME ,
CASE WHEN ( a.ORDINAL_POSITION = 1
AND p1.value IS NOT NULL
) THEN p1.value
ELSE ''
END AS TABLE_Description ,
a.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX('char', a.DATA_TYPE) > 0
OR CHARINDEX('binary', a.DATA_TYPE) > 0
)
AND a.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN a.DATA_TYPE + '('
+ CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
WHEN ( ( CHARINDEX('CHAR', a.DATA_TYPE) > 0
OR CHARINDEX('binary', a.DATA_TYPE) > 0
)
AND a.CHARACTER_MAXIMUM_LENGTH = -1
) THEN a.DATA_TYPE + '(max)'
WHEN ( CHARINDEX('numeric', a.DATA_TYPE) > 0 )
THEN a.DATA_TYPE + '(' + CAST(a.NUMERIC_PRECISION AS VARCHAR(4))
+ ',' + CAST(a.NUMERIC_SCALE AS VARCHAR(4)) + ')'
ELSE a.DATA_TYPE
END AS COLUMN_TYPE ,
CASE WHEN c.IS_IDENTITY = 1 THEN 'YES'
ELSE 'NO'
END AS IS_IDENTITY ,
a.IS_NULLABLE ,
CASE WHEN a.COLUMN_DEFAULT IS NULL THEN ''
ELSE a.COLUMN_DEFAULT
END AS Default_Value ,
CASE WHEN p.value IS NULL THEN ''
ELSE p.value
END AS [COLUMN_Description] ,
CASE WHEN o.name IS NULL THEN ''
ELSE '√'
END AS Is_PrimaryKey ,
CASE WHEN f.parent_column_id IS NULL THEN ''
ELSE '√'
END AS Is_Foreignkeys ,
CASE WHEN referenced_object_id IS NULL THEN ''
ELSE OBJECT_NAME(referenced_object_id)
END AS Foreign_Table ,
CASE WHEN referenced_object_id IS NULL THEN ''
ELSE ( SELECT name
FROM sys.columns
WHERE object_id = f.referenced_object_id
AND column_id = f.referenced_column_id
)
END AS Foreign_key
FROM INFORMATION_SCHEMA.COLUMNS a
INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = c.OBJECT_ID
AND a.COLUMN_NAME = c.NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.COLUMN_NAME = b.COLUMN_NAME
LEFT JOIN sys.sysobjects o ON o.name = b.CONSTRAINT_NAME
AND o.xtype = 'PK'
LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = p.major_id
AND a.Ordinal_position = p.minor_id
AND p.class_desc = 'OBJECT_OR_COLUMN'
LEFT JOIN sys.extended_properties p1 ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = p1.major_id
AND p1.minor_id = 0
LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA + '.'
+ a.TABLE_NAME) = f.parent_object_id
AND a.ORDINAL_POSITION = f.parent_column_id
WHERE a.TABLE_NAME = 'Address'
-- a.TABLE_NAME IN (SELECT name FROM sys.tables)
ORDER BY a.TABLE_SCHEMA,a.TABLE_NAME, a.ORDINAL_POSITION
運行效果如圖：
有不對的地方還請大家拍磚！謝謝！
相關(guān)文章
Mysql 如何實現(xiàn)多張無關(guān)聯(lián)表查詢數(shù)據(jù)并分頁
這篇文章主要介紹了Mysql 實現(xiàn)多張無關(guān)聯(lián)表查詢數(shù)據(jù)并分頁的操作，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-06-06
這篇文章主要介紹了MySQL中鎖的相關(guān)問題,本文給大家介紹了從對數(shù)據(jù)操作的粒度分 ，從對數(shù)據(jù)操作的類型分，給大家介紹的非常詳細(xì)，需要的朋友可以參考下2021-12-12
本篇文章是對Mysql中OPTIMIZE TABLE的作用進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
Can''t connect to local MySQL through socket ''/tmp/mysql.so
今天小編就為大家分享一篇關(guān)于Can't connect to local MySQL through socket '/tmp/mysql.sock'解決方法，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
Mysql 5.7.17 winx64在win7上的安裝教程
本文給大家介紹Mysql 5.7.17 winx64在win7上的安裝教程，非常不錯，具有參考借鑒價值，需要的朋友參考下吧2017-04-04
本文主要介紹了MySQL數(shù)據(jù)表添加字段的三種方式，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
索引條件下推,也叫索引下推,英文全稱Index?Condition?Pushdown,簡稱ICP,索引下推是MySQL5.6新添加的特性,用于優(yōu)化數(shù)據(jù)的查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL索引下推的相關(guān)資料,需要的朋友可以參考下2022-10-10
windows 10 下安裝mysql 5.7.17的簡單筆記
之前一直在Linux下用MySQL，安裝也很簡單，今天試一下windows下安裝，發(fā)現(xiàn)有很多坑，今天小編通過本教程給大家記錄下，一起看看吧2016-12-12
深入mysql "ON DUPLICATE KEY UPDATE" 語法的分析
本篇文章是對mysql "ON DUPLICATE KEY UPDATE"語法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
在日常的項目開發(fā)當(dāng)中,經(jīng)常會遇到獲取同一屬性相同的記錄,如何獲取記錄時間最新的那一條,下面這篇文章主要給大家介紹了關(guān)于SQL結(jié)果如何根據(jù)某個字段取最新時間去重的相關(guān)資料,需要的朋友可以參考下2023-06-06