SELECT
(casewhen a.colorder=1then d.nameelse '' end) as 表名,--如果表名相同就返回空 syscolumns(表字段信息表) a sysobjects d
a.colorderas 字段序号,
a.nameas 字段名,
(casewhen COLUMNPROPERTY(a.id,a.name,'IsIdentity' )=1 then '√' else'' end)as 标识, --返回IsIdentity的值，IsIdentity只有两个值：、
(casewhen(SELECTcount(*) FROM sysobjects--查询主键
WHERE(namein
(SELECTname FROM sysindexes
WHERE (id = a.id) AND(indidin
(SELECT indid FROM sysindexkeys
WHERE(id = a.id)AND(colidin
(SELECT colid FROM syscolumns
WHERE(id = a.id)AND(name= a.name))
)))))
AND(xtype= 'PK' ))>0 then '√'else '' end) as 主键,--查询主键END
b.nameas 类型, --systypes b
a.lengthas 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(casewhen a.isnullable=1then '√' else '' end)as 允许空,
isnull(e.text,'') as 默认值, --syscomments e
isnull(g.[value],'') AS 字段说明 --sys.extended_properties g (字段信息表)
FROM syscolumnsa left join systypes b
on a.xtype=b.xusertype
inner joinsysobjects d
on a.id=d.idand d.xtype='U'and d.name<>'dtproperties'
left joinsyscomments e
on a.cdefault=e.id
left joinsys.extended_propertiesg
on a.id=g.major_idAND a.colid= g.minor_id
where d.name='systempara'--所要查询的表
order by a.id,a.colorder