数据字典
SELECT
sysobjects.name AS 表名称 ,
–sysproperties.[value] AS 表说明 ,
syscolumns.name AS 字段名称 ,
–properties.[value] AS 字段说明 ,
systypes.name AS 字段类型 ,
syscolumns.length AS 字段长度 ,
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,\’Scale\’), 0) AS 小数位数 ,
CASE WHEN syscolumns.isnullable=0
THEN \’\’
ELSE \’ √ \’
END AS 是否为空 ,
CASE WHEN syscomments.text IS NULL
THEN \’\’ ELSE syscomments.text
END AS 缺省值 ,
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, \’IsIdentity\’)= 1
THEN \’ √ \’ ELSE \’\’
END AS 递增字段 ,
CASE WHEN sysindexes.name IS NULL
THEN \’\’
ELSE sysindexes.name
END AS 索引名称 ,
CASE WHEN sysindexkeys.keyno IS NULL
THEN \’\’
ELSE CONVERT(VARCHAR(10),sysindexkeys.keyno )
END AS 索引位置 ,
CASE WHEN sysindexes.indid=1
THEN \’ 聚集索引 \’
WHEN sysindexes.indid>1 AND sysindexes.indid<>255
THEN \’ 非聚集索引 \’
WHEN sysindexes.indid IS NULL
THEN \’\’
ELSE
\’ 其他 \’
END AS 索引类型 ,
CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = \’PK\’ AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN \’ √ \’ ELSE \’\’
END AS 主键 ,
CASE WHEN sysforeignkeys.constid IS NULL
THEN \’\’
ELSE \’ √ \’
END AS 外健
FROM syscolumns — 数据表字段
INNER JOIN sysobjects — 数据对象
ON sysobjects.id = syscolumns.id
INNER JOIN systypes — 数据类型
ON syscolumns.xtype = systypes.xtype
–LEFT OUTER JOIN sysproperties properties — 字段属性信息
— ON syscolumns.id = properties.id
— AND syscolumns.colid = properties.smallid
–LEFT OUTER JOIN sysproperties — 表属性信息
— ON sysobjects.id = sysproperties.id
— AND sysproperties.smallid = 0
LEFT OUTER JOIN syscomments — 注释信息
ON syscolumns.cdefault = syscomments.id
LEFT OUTER JOIN sysindexkeys — 索引中的键或列的信息
ON sysindexkeys.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
LEFT OUTER JOIN sysindexes — 数据库 索引表
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
LEFT OUTER JOIN sysforeignkeys
ON sysforeignkeys.fkeyid = syscolumns.id
AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype = \’U\’)
order by sysobjects.id,syscolumns.colid