Query table columns schema for SQLServer(Test in 2008)

gistfile1.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 sys.extended_properties g ON a.id = g.major_id
                                           AND a.colid = g.minor_id
WHERE
    d.name = 'tbl_name'
ORDER BY
    OBJECT_NAME(a.id),
    a.colorder