解析SQL 表结构信息查询 含主外键、自增长

最近项目需要做什么数据字典，需要表结构信息。在网上看了许多关于表结构信息的查询，感觉都不怎么样。相对好一点就是《基于sql2005 sql2008 表结构信息查询升级版的详解(含外键信息)》 ，但是这里有一点小问题，缺少一个过滤以致运行有一点小bug。在adventureworks2012数据库中的address表查询结果如图：

在查询过滤中我们添加以下信息就ok了：
and g.class_desc = 'object_or_column'
修改后的sql如下：

select  表名 = case when a.colorder = 1 then d.name
                  else ''
             end ,
        表说明 = case when a.colorder = 1 then isnull(f.value, '')
                   else ''
              end ,
        字段序号 = a.colorder ,
        字段名 = a.name ,
        标识 = case when columnproperty(a.id, a.name, 'isidentity') = 1 then '√'
                  else ''
             end ,
        主键 = case when exists ( select  1
                                from    dbo.sysindexes si
                                        inner join dbo.sysindexkeys sik on si.id = sik.id
                                                              and si.indid = sik.indid
                                        inner join dbo.syscolumns sc on sc.id = sik.id
                                                              and sc.colid = sik.colid
                                        inner join dbo.sysobjects so on so.name = so.name
                                                              and so.xtype = 'pk'
                                where   sc.id = a.id
                                        and sc.colid = a.colid ) then '√'
                  else ''
             end ,
        外键 = case when tony.fkey is not null
                       and tony.fkey = a.colid then '√'
                  else ''
             end ,
        外键表 = case when tony.fkey is not null
                        and tony.fkey = a.colid then object_name(tony.fkeyid)
                   else ''
              end ,
        外键字段 = case when tony.fkey is not null
                         and tony.fkey = a.colid
                    then ( select   name
                           from     syscolumns
                           where    colid = tony.fkey
                                    and id = tony.fkeyid
                         )
                    else ''
               end ,
        类型 = b.name ,
        长度 = a.length ,
        精度 = columnproperty(a.id, a.name, 'precision') ,
        小数位数 = isnull(columnproperty(a.id, a.name, 'scale'), 0) ,
        允许空 = case when a.isnullable = 1 then '√'
                   else ''
              end ,
        默认值 = isnull(e.text, '') ,
        字段说明 = isnull(g.[value], '') ,
        创建时间 = d.crdate ,
        更改时间 = case when a.colorder = 1 then d.refdate
                    else null
               end
from    dbo.syscolumns a
        left join dbo.systypes b on a.xtype = b.xusertype
        inner join dbo.sysobjects d on a.id = d.id
                                       and d.xtype = 'u'
                                       and d.status >= 0
        left join dbo.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
                                               and g.class_desc = 'object_or_column'
        left join sys.extended_properties f on d.id = f.major_id
                                               and f.minor_id = 0
        left join sysobjects htl on htl.parent_obj = d.id
                                    and htl.xtype = 'f'
        left join sysforeignkeys tony on htl.id = tony.constid
where   d.name = 'address'  --这里输入包含表名称的条件
order by d.id ,
        a.colorder

运行结果如图：

我不怎么喜欢它的“类型”信息，一般的varchar都会有长度信息，还有这个查询对于sql 2012的新数据类型不支持，该sql里面的嵌套查询比较多，于是我就自己重新写了一个sql。
这里提醒大家尽量用information_schema.xxx视图而不去用sys.xxx视图。
新的sql如下：

select
 --object_id(a.table_schema + '.' + a.table_name) as [object_id] ,
        case when a.ordinal_position = 1
             then a.table_schema + '.' + a.table_name
             else ''
        end as table_name ,
        case when ( a.ordinal_position = 1
                    and p1.value is not null
                  ) then p1.value
             else ''
        end as table_description ,
        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 column_type ,
        case when c.is_identity = 1 then 'yes'
             else 'no'
        end as is_identity ,
        a.is_nullable ,
        case when a.column_default is null then ''
             else a.column_default
        end as default_value ,
        case when p.value is null then ''
             else p.value
        end as [column_description] ,
        case when o.name is  null then ''
             else '√'
        end as is_primarykey ,
        case when f.parent_column_id is null then ''
             else '√'
        end as is_foreignkeys ,
        case when referenced_object_id is null then ''
             else object_name(referenced_object_id)
        end as foreign_table ,
        case when referenced_object_id is null then ''
             else ( select  name
                    from    sys.columns
                    where   object_id = f.referenced_object_id
                            and column_id = f.referenced_column_id
                  )
        end as foreign_key
from    information_schema.columns a
        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 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
        left join sys.sysobjects o on o.name = b.constraint_name
                                      and o.xtype = 'pk'
        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.extended_properties p1 on object_id(a.table_schema + '.'
                                                          + a.table_name) = p1.major_id
                                                and p1.minor_id = 0
        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'
-- a.table_name in (select name from sys.tables)
order by a.table_schema,a.table_name, a.ordinal_position

运行效果如图：

有不对的地方还请大家拍砖！谢谢！