select case when c.colid = 1 then o.name else '' end 表名, -- 得到表名，重復的表名不顯示
c.colid順序,
c.name字段名,
t.name字段類型,
columnproperty (c.id,c.name, ' PRECISION ' )字段長度,
isnull (c.Scale, '' )小數位,
c.length占用字節,
case when c.isnullable = 1 then ' √ ' else '' end 可為空,
case when c.colid in ( select ik.colid
from sysindexesi,Sysindexkeysik,sysobjectsoo
where i.id = ik.id and i.indid = ik.indid
and i.name = oo.name and oo.xtype = ' PK ' -- 主鍵
and o.id = i.id
) then ' √ ' else '' end 主鍵,
case when c.colid in ( select ik.colid
from sysindexesi,Sysindexkeysik
where i.id = ik.id and i.indid = ik.indid
and o.id = i.id and i.indid = 1 -- 聚類索引
) then ' √ ' else '' end 聚類索引,
case when columnproperty (c.id,c.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end 自增長,
isnull (m. text , '' )默認值
from sysobjectso,syscolumnsc,systypest,syscommentsm
where o.xtype = ' U '
and o.id = c.id
and c.xtype = t.xtype
and c.cdefault *= m.id
order by o.name,c.colid