SELECT 表名 = (CASE WHEN b.colorder=1 THEN a.name
                  ELSE ''
             END),
--表说明=( case when b.colorder=1 then c.value else '' end),
    b.colid 序号,b.name 字段名,
    标识 = CASE WHEN COLUMNPROPERTY(b.id,b.name,'IsIdentity')=1 THEN '√'
              ELSE ''
         END,
    主键 = CASE WHEN EXISTS ( SELECT 1
                              FROM sys.sysobjects
                              WHERE xtype='PK' AND parent_obj=b.id AND name IN (
                                SELECT name
                                  FROM sys.sysindexes
                                  WHERE indid IN (
                                    SELECT indid
                                      FROM sys.sysindexkeys
                                      WHERE id=b.id AND colid=b.colid)) )
              THEN '√'
              ELSE ''
         END,类型 = d.name,b.length 占用字节,
    长度 = COLUMNPROPERTY(b.id,b.name,'Precision'),
    允许空 = CASE WHEN COLUMNPROPERTY(b.id,b.name,'AllowsNull')=1 THEN '√'
               ELSE ''
          END,小数位数 = COLUMNPROPERTY(b.id,b.name,'Scale'),
    默认值 = CASE WHEN e.text IS NULL THEN ''
               ELSE e.text
          END,字段说明 = ISNULL(f.value,'')
  FROM sys.sysobjects a
    INNER  JOIN sys.syscolumns b
    ON a.id=b.id AND a.xtype='U' AND a.name<>'dtproperties'
    LEFT JOIN sys.extended_properties c
    ON a.id=c.major_id AND c.minor_id=0
    LEFT JOIN sys.systypes d
    ON b.xusertype=d.xusertype
    LEFT JOIN sys.syscomments e
    ON e.id=b.cdefault
    LEFT JOIN sys.extended_properties f
    ON b.id=f.major_id AND b.colid=f.minor_id
  WHERE a.name='XXXX'
  ORDER BY b.colid --a.name='XXXX'表示查看XXX表的结构信息