最近项目需要做什么数据字典，需要表结构信息。在网上看了许多关于表结构信息的查询，感觉都不怎么样。相对好一点就是《
基于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
运行结果如图：
我不怎么喜欢它的“类型”信息，一般的varchar都会有长度信息，还有这个查询对于SQL 2012的新数据类型不支持，该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
运行效果如图：
有不对的地方还请大家拍砖！谢谢！
易网时代-编程资源站