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,'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
