Sql中获取表结构（字段名称，类型，长度，说明）

浏览数：44 / 时间：2015年06月12日

在写代码生成器的时候遇到这样一个问题，想在搭建好数据库后把字段说明当做注释写进类文件里，所以我们在网上搜索到了许多代码很长很长的方法（当然我的代码也很长），亲测了一条简单易懂的语句，也是大多数转载的方法：

SELECT 
TableName = OBJECT_NAME(c.object_id), 
ColumnsName = c.name, 
Description = ex.value, 
ColumnType=t.name, 
Length=c.max_length 
FROM 
sys.columns c 
LEFT OUTER JOIN 
sys.extended_properties ex 
ON 
ex.major_id = c.object_id 
AND ex.minor_id = c.column_id 
AND ex.name = ‘MS_Description‘ 
left outer join 
systypes t 
on c.system_type_id=t.xtype 
WHERE 
OBJECTPROPERTY(c.object_id, ‘IsMsShipped‘)=0 
AND OBJECT_NAME(c.object_id) =‘表名‘

亲测可用没问题，但是在测试的时候出现这样一个问题

技术分享

我的表中的确是有Title 和Category字段，但是为什么查出来的结果有两条呢？

看ColumnType 不难理解，这两个一个是我自己的表中的字段，另一个是系统保留字段，那么问题又回到了我最初使用的语句：

select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from information_schema.columns where TABLE_NAME=‘表名‘

这样一来查询结果是这样的：

技术分享

结果中整形和ntext类型是我不需要长度的类型，结果是NULL正合我意，但是这里没有我要的字段说明。所以我打算两个语句结合起来试一下，在这之前我们需要了解一下第一个查询语句中有哪些是我们需要的。

于是我分别尝试了查询以下3个表的内容，为了解它的结构。我们看到

select * from sys.extended_properties
select * from information_schema.columns
select * from sys.columns

首先是select * from sys.extended_properties

结果如下：

技术分享

那么所有的表的描述都在这里了

然后我们再来看

select * from information_schema.columns

select * from sys.columns

这两张表里面内容很多，这里就不截图了，大家可以自己查一下看看内容。

经过比较以后得出结论：在我们最先搜索到的方法中OBJECT_NAME(c.object_id)是将sys.columns中的object_id转换成表名的方法，但是sys.columns中没有我们需要的信息，所以OBJECT_NAME()方法留着备用；

其次我们在上一个截图中sys.extended_properties表看到了major_id这个字段，这个字段有什么含义？于是我从sys.columns查询了一下sys.columns.object_id=sys.extended_properties.major_id的内容，比如上述截图中的

select * from sys.columns where object_id=613577224

得到的结果正好是同一张表中的字段信息，于是得出结论：

表sys.extended_properties中的major_id和sys.columns中object_id是相关联的（主键外键关系？）

终于我们找到了sys.extended_properties表和information_schema.columns表中共有的字段，那就是：

sys.extended_properties.major_id和information_schema.columns.TABLE_NAME

因为sys.columns.object_id就是sys.extended_properties.major_id 那么OBJECT_NAME(sys.extended_properties.major_id)=information_schema.columns.TABLE_NAME就是架起两者之间关系的桥梁了

说了一堆废话，看最终的语句：

select a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_MAXIMUM_LENGTH,b.value 
from information_schema.COLUMNS as a  left join sys.extended_properties as b 
on a.TABLE_NAME=OBJECT_NAME(b.major_id) and a.ORDINAL_POSITION=b.minor_id where a.TABLE_NAME=‘表名‘