SELECT 表名=case WHEN a.colorder=1 THEN d.name ELSE '' end

     , 表说明=case  WHEN a.colorder=1 THEN isnull(f.value,'') ELSE '' end, 字段序号=a.colorder

, 字段名=a.name, 标识=case WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√'else '' end

, 主键=case

    WHEN exists

    (SELECT 1

    FROM sysobjects

    WHERE xtype='PK'

            AND name IN 

        (SELECT name

        FROM sysindexes

        WHERE indid IN 

            (SELECT indid

            FROM sysindexkeys

            WHERE id = a.id

                    AND colid=a.colid))) THEN

            '√'

    ELSE '' end, 类型=b.name, 占用字节数=a.length, 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空=case

    WHEN a.isnullable=1 THEN

    '√'else '' end

, 默认值=isnull(e.text,'')

, 字段说明=isnull(g.[value],'')

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.major_id

        AND f.minor_id =0 

--where d.name='要查询的表' --如果只查询指定表,加上此条件

ORDER BY  a.id,a.colorder