MyException - 我的异常网
当前位置:我的异常网» SQL » SQL2005 SQL2008 表构造信息查询 含主外键、自增长

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

www.myexceptions.net  网友分享于:2013-04-10  浏览:10次
SQL2005 SQL2008 表结构信息查询 含主外键、自增长

最近在做数据字典的一些文档,需要表结构信息。在网上看了许多关于表结构信息的查询,感觉都不怎么样于是就自己写了一个,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



文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有