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