1. SQL Server 2000数据库字典（表结构.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 COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS(SELECT 1FROM dbo.sysindexes si INNER JOINdbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOINdbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOINdbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 答应空, ISNULL(e.text, '') AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间FROM dbo.syscolumns a LEFT OUTER JOINdbo.systypes b ON a.xtype = b.xusertype INNER JOINdbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOINdbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOINdbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND g.name = 'MS_Description' LEFT OUTER JOINdbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND f.name = 'MS_Description'ORDER BY d.name, a.colorder
◆SQL Server 2005数据库字典（表结构.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 COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS(SELECT 1FROM dbo.sysindexes si INNER JOINdbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOINdbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOINdbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 答应空, ISNULL(e.text, '') AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间FROM dbo.syscolumns a LEFT OUTER JOINdbo.systypes b ON a.xtype = b.xusertype INNER JOINdbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOINdbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOINsys.extended_properties g ON a.id = g.major_id AND a.colid = g.major_id AND g.name = 'MS_Description' LEFT OUTER JOINsys.extended_properties f ON d.id = f.major_id AND f.major_id = 0 AND f.name = 'MS_Description'ORDER BY d.name, a.colorder
2.SQL Server数据库字典（索引.sql）
SELECT TOP 100 PERCENT --a.id, CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名, CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名, b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id, a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一, CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束, a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间FROM dbo.sysindexes a INNER JOIN dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK'WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)ORDER BY c.name, a.name, b.keyno转载于:https://www.cnblogs.com/lfzwenzhu/archive/2009/06/10/1500313.html
