SQL获得表结构

SELECT
 ( CASE   WHEN  a.colorder = 1   THEN  d.name  ELSE   ''   END ) N ' 表名 ' ,
 a.colorder N ' 字段序号 ' ,
 a.name N ' 字段名 ' ,
 ( CASE   WHEN   COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1   THEN   ' √ ' ELSE   ''   END ) N ' 标识 ' ,
 ( 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 ) N ' 主键 ' ,
 b.name N ' 类型 ' ,
 a.length N ' 占用字节数 ' ,
  COLUMNPROPERTY (a.id,a.name, ' PRECISION ' )  AS  N ' 长度 ' ,
  ISNULL ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )  AS  N ' 小数位数 ' ,
 ( CASE   WHEN  a.isnullable = 1   THEN   ' √ ' ELSE   ''   END ) N ' 允许空 ' ,
  ISNULL (e. text , '' ) N ' 默认值 ' ,
  ISNULL (g. [ value ] , '' )  AS  N ' 字段说明 '
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  sysproperties g  on  a.id = g.id  AND  a.colid = g.smallid 
WHERE
 d.name  =   ' community '   -- 要查询的表
ORDER   BY
  object_name (a.id), a.colorder