简单的3个SQL视图搞定所有SqlServer数据库字典 (本来要直接收藏但是不能网摘).docx

window 命令大全 (已更改）网上有很多 SQL SERVER 数据库字典的 SQL 语句，七零八落，我在工作整理了一下思路，总结 SQL 代码如下。数据库字典包括表结构(分 2K 和2005）、索引和主键. 外键. 约束. 视图. 函数. 存储过程. 触发器。可以在企业管理器、查询分析器中简单执行，直接了当的查出 SQL2K 及 SQL2005 的所有数据字典，方便文档的编写，希望对大家有帮助。 SqlServer2000 数据库字典-- 表结构.sql SELECT TOP 100 PERCENT --a.id, CASE WHEN a.colorder = 1 THEN d.name ELSE END AS 表名, CASE WHEN a.colorder = 1 THEN isnull (f.value, ) ELSE END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPRO PERTY (a.id, a.name, IsIdentity ) = CASE WHEN EXISTS ( SELECT 1 1 THEN √ ELSE END AS 标识, FROM dbo.sysindexes si INNER JOIN id dbo.sysindexkeys sik = sik.indid INNER JOIN ON si.id = sik.id AND si.ind dbo.syscolumns sc ON sc.id = sik.id AND sc.coli d = sik.colid INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xty pe = PK WHERE sc.id = a.id AND sc.colid = a.colid) THEN √ ELS E END AS 主键, b.name AS 类型, a.length AS 长度, COLUMNPROPERTY (a.id, a.nam e, PRECISION ) AS 精度, ISNULL ( COLUMNPROPERTY (a.id, a.name, Scale ), 0 ) A S 小数位数, CASE WHEN a.isnullable = 1 THEN √ ELSE END AS 允许空, ISNULL (e. text , ) AS 默认值, ISNULL (g. [value] , ) AS 字段说明, d.crdate AS 创 建时间,CASE 建时间, CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更 改时间 FROM dbo.syscolumns a LEFT OUTER 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 OUTER JOIN dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN dbo.sysproperties g ON a.id = g.id AND a.colid = g.smalli d LEFT OUTER JOIN dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 ORDER BY d.name, a.colorder SELECT TOP ( 100 ) PERCENT --a.id, CASE WHEN a.colorder = 1 THEN d .name E END AS 表名, CASE WHEN a.colorder = 1 THEN isnull (f.value, ELS ) ELSE WHEN END AS 表说明, a.colorder AS 字段序 号, a.name AS ty ) 字段名, CASE COLUMNPROPERTY (a.id, a.name, IsIdenti = 1 THEN √ ELSE END AS 标识, CASE WHE N EXISTS ( SELECT 1 FROM dbo.sysindexes si INNE R JOIN dbo.sysindexk eys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.sysco