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