SQL2005 SQL2008 表结构信息查询 含主外键、自增长

来源：http://www.cnblogs.com/majiang/archive/2013/04/09/3009382.html

最近在做数据字典的一些文档，需要表结构信息。在网上看了许多关于表结构信息的查询，感觉都不怎么样于是就自己写了一个，sql 如下：

?

SELECT  OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) AS [object_id] ,

        a.TABLE_SCHEMA + '.' + a.TABLE_NAME as  TABLE_NAME,

        a.COLUMN_NAME ,

        CASE WHEN ( (CHARINDEX('char', a.DATA_TYPE) > 0 OR CHARINDEX('binary', a.DATA_TYPE) > 0)

                    AND a.CHARACTER_MAXIMUM_LENGTH <> -1

                  )

             THEN a.DATA_TYPE + '('+ CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'

             WHEN ( (CHARINDEX('CHAR', a.DATA_TYPE) > 0 OR CHARINDEX('binary', a.DATA_TYPE) > 0)

                    AND a.CHARACTER_MAXIMUM_LENGTH = -1

                  ) THEN a.DATA_TYPE + '(max)'

 

             WHEN ( CHARINDEX('numeric', a.DATA_TYPE) > 0)

                  THEN a.DATA_TYPE + '('+CAST(a.NUMERIC_PRECISION AS VARCHAR(4))+','+CAST(a.NUMERIC_SCALE AS VARCHAR(4))+')'

             ELSE a.DATA_TYPE

        END AS DATA_TYPE ,

        c.IS_IDENTITY,

        a.IS_NULLABLE ,

        a.COLUMN_DEFAULT ,

        b.COLUMN_NAME AS PrimaryKey ,

        p.value AS [Description] ,

        CASE WHEN f.parent_column_id IS NULL THEN 'No'

             ELSE 'yes'

        END AS is_foreign_keys ,

        OBJECT_NAME(referenced_object_id) AS Foreign_Table ,

        ( SELECT    name

          FROM      sys.columns

          WHERE     object_id = f.referenced_object_id

                    AND column_id = f.referenced_column_id

        ) AS Foreign_keys

FROM    INFORMATION_SCHEMA.COLUMNS a

        LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA

                                                           AND a.TABLE_NAME = b.TABLE_NAME

                                                           AND a.COLUMN_NAME = b.COLUMN_NAME

        INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME)=c.OBJECT_ID

                                              AND a.COLUMN_NAME=c.NAME

        LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) = p.major_id

                                               AND a.Ordinal_position = p.minor_id

                                               AND p.class_desc = 'OBJECT_OR_COLUMN'

        LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) = f.parent_object_id

                                               AND a.ORDINAL_POSITION = f.parent_column_id

WHERE   a.TABLE_NAME = 'Address'

ORDER BY a.ORDINAL_POSITION