1.查询数据库表结构
select
(case when a.colorder=1 then d.name else null end) [表名],
a.colorder [序号],
a.name [字段名],
(case when columnproperty(a.id,a.name,'IsIdentity')=1 then '√'else '' end) [标识],
(case when (select count(id)
from sysobjects
where (name in (select name
from sysindexes
where (id = a.id)
and (indid in (select indid
from sysindexkeys
where (id = a.id)
and (colid in (select colid
from syscolumns
where (id = a.id)
and (name = a.name)))))))
and (xtype = 'PK'))>0
then '√' else ''
end) [主键],
b.name [类型],
a.length [占用字节数],
columnproperty(a.id,a.name,'PRECISION') [长度],
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) [小数位数],
(case when a.isnullable=1 then '√'else '' end) [允许空],
isnull(e.text,'') [默认值],
isnull(g.[value], ' ') [备注]
from syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id
and d.xtype='U'
and d.name not in ('dtproperties','sysdiagrams')
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties g
on a.id=g.major_id
and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
order by d.name --,a.id,a.colorder