MSSQL查询表结构

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(*)                     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') as [长度],       isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) as [小数位数],       (case when a.isnullable = 1 then '√' else  '' end) [允许空],       isnull(e.text, '') [默认值],       isnull(g.[value], ' ') AS [说明]  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 <> 'dtproperties'  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 a.id,a.colorder