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

youdial

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

• [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  

  
  

cxwpf200

fdhfgh

人生重要的不是所站的位置，而是所朝的方向!

lichaoyue888

学习了，谢谢分享、、、

q66262

打破老婆终身制，实行小姨股份制。引入小姐竞争制，推广情人合同制。

2168575

只有假货是真的，别的都是假的!

civilvar

所有刻骨铭心的爱都灵魂游离于床上的瞬间！