最近项目需要做什么数据字典，需要表结构信息。在网上看了许多关于表结构信息的查询，感觉都不怎么样。相对好一点就是《
》 ，但是这里有一点小问题，缺少一个过滤以致运行有一点小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
运行效果如图：
有不对的地方还请大家拍砖！谢谢！
如对本文有疑问，
点击进行留言回复！！
我要评论
网友评论