sql server查询表结构（字段名，数据类型，长度，描述，是否允许为空，是否为主键）

转自：http://www.xxling.com/blog/article/58.aspx

一、为什么要读取表结构

对于一个程序员的平常工作当中，我们常用的都是用来从数据库表中读取数据的sql，而对于表结构的读取就比较少用了。因为有客户端，没事我们也没必要通过sql语句就读取而查找表结构之类的东西。但是在以下的一些情况中可能我们就要用到了。比如：

1.要写一个实体生成器的时候，我们就得读取表的字段、类型等等之类的东西；

2.如果要做一个数据库管理系统，我们不仅要读取结构，我们还得知道怎么去添加和修改这些结构（不过添加和修改不在这篇文章的介绍内容内）；

当然还会有其他的一些用到的情况，这里就不一一列举了。

二、怎么入手

当我们想到要做读取表结构这件事时，我们该如何入手呢。如果我们已经是这方面的专家或已经熟悉了这方面的知识的话，那么我们需要的就是动手去做了。但如果你之前对这一无所知或者了解不够的时候，我们该怎么做呢？

今天我们就说说第二种情况我们该怎么做吧。为什么要说这呢？因为每个人的知识都是有限的，而且不可能每一次在你需要的时候都有人在身边告诉你怎么去做，所以我们就要学会如何用手上可以用的资源来寻找到我们想要的东西。

如果我们完全不知道的情况下，第一，我们可以把我们知道的关键字到搜索引擎去获取一些知识和答案，然后我们根据这些知识可以做进一步的检索或者根据自己已有的经验去引导自己去摸索和认识。第二，我们可以向身边有经验的同事或者同学询问。第三，我们也可以到一些技术论坛发帖提问来寻找答案。学会自己去找答案，这是很重要的，这是我为什么要啰嗦这一部分的原因。

三、需求

今天我们想要读取的结构包括：数据库中的所有用户表，表的字段名，字段类型，字段长度限制，字段描述，是否允许为空，是否为主键。

四、有哪些方案

这里我先说明一下，今天说的是sql server 2005及以上的版本，因为2000版本的会有一些差别，后面会提到。

通过我们掌握的知识，我们知道，数据库表结构相关的元数据是存储在数据库的系统表里的。那我们先看数据库的表结构信息都可以在哪里找得到。

首先打开我们要操作的数据库，展开视图中的系统视图选项，我们就可以看到一大堆的分别以INFORMATION_SCHEMA.XXX,sys.sysXXX和sys.XXX形式存在的视图。这些视图都是些什么呢？

以INFORMATION_SCHEMA.XXX形式存在的视图我们称之为信息架构视图，它们是在系统表的基础上建立的一系列视图，提供了更加直观的结果。2000版本时是在master数据库中，而从2005版本开始，这些视图被移到了每个数据库中去了。其次这些视图是符合ISO标准的。

以sys.sysXXX形式存在视图我们称之为兼容性视图，顾名思义这些视图就是为了向后兼容而存在的，在2000版本时这些视图就已经存在但未公开，只公开了在master中的系统表。2005版本之后公开了这些视图并放在每个数据库中。

以sys.XXX形式存在的视图我们称之为目录视图，是2005版本之后弄出来的东东。

那么具体这些视图中每个视图又都分别有什么数据呢？如果你手头有完整的介绍，那就直接看介绍就好。否则，首先我们可以通过它们的命名来判断它们存储的相关内容，其次我们可以将它们的结果搜索出来看看里面到底是什么东西，而对于每个字段是什么意思，我们可以通过F1去查找到相关的帮助文档。

根据第三部分的需求，我们一一找到数据所在的位置，如下：
数据库的用户表列表：sys.sysobjects或者sys.tables或者INFORMATION_SCHEMA.TABLES
表的字段名：sys.syscolumns或者sys.columns或者INFORMATION_SCHEMA.COLUMNS
字段类型：sys.syscolumns（加上sys.systypes）或者sys.columns（加上sys.types）或者INFORMATION_SCHEMA.COLUMNS
字段长度：sys.syscolumns或者sys.columns或者INFORMATION_SCHEMA.COLUMNS
字段描述：sys.extended_properties
是否允许为空：sys.syscolumns或者sys.columns或者INFORMATION_SCHEMA.COLUMNS
是否为主键：sys.sysobjects（加上sys.sysindexes和sys.sysindexkeys）或者sys.indexes（加上sys.index_columns）或者INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
注：括号中的表都是需要他们联合才能拿到需要的信息的。比如sys.sysobjects没有与字段id建立关系，所以才加上sys.sysindexes和sys.sysindexkeys这两张表做桥梁来建立关系

五、如何去选择

首先，兼容性视图就是为了兼容而存在，而今天我们只考虑2005版本及以上的版本所以不在推荐使用，可以将它pass掉

其次，我们知道INFORMATION信息架构视图里面的信息会更加的直观而且符合ISO标准，理论上这是我们不二的选择。但是从第四部分的结果可以看出，INFORMATION的视图中找不到我们需要的字段描述的信息（当然可以借助其他办法配合拿到相应的信息）。相反，目录视图中可以找到所有的信息，所以也是一种选择

综上，我们应该选择信息架构视图或者目录视图。而信息结构视图的信息比较直观就不多说，这次主要是以目录视图来开讲，当然最终会给出三种方案的结果。

六、具体实现

1.读取数据库的所有表
select name from sys.objects where type = 'U'
或者
select name from sys.tables where type = 'U'

2.读取表结构

首先，我们先找出表之间的关联以获取表联合的条件。

所有表如下：
sys.columns
sys.types
sys.extended_properties
sys.objects（也可以用sys.tables，它是sys.objects派生表）
sys.indexes
sys.index_columns

既然我们读取的是表结构，那么首先我们的限定范围就应该是表，所以我们先要找到table对应的id，所以我们先从sys.objects表开始。我们可以通过表名进行过滤得到所要表的id。代码如下：

select object_id from sys.objects where name=@TableName

@TableName为存储表名字的一变量，一下涉及到表名的地方都用该变量替代。该变量可按如下定义

declare @TableName varchar(50)

set @TableName='XXXX'

得到了table的id我们就可以通过这id作为筛选条件来得到关于这张表的一些信息，可以用作where条件也可以通过join联合的方式，在这里我们选用join的方式吧。

表字段的信息主要是存在sys.columns表，所以我们紧跟着就从这张表查找一部分信息，然后在联合其他表去查其他信息。

在sys.columns中我们可以获得的需要的信息有字段名（name），字段类型的id（user_type_id），字段的限制长度（max_length），是否允许为空（is_nullable）。

其中我们容易迷惑的有如下：

（1）在读取字段类型时我们发现此表中有一个叫system_type_id的字段和一个user_type_id的字段,那他们之间有什么区别呢？顾名思义，前面一system为前缀的就是系统类型的id，而以user为前缀的就是用户类型的id，其中用户类型包含了系统类型，用户类型是基于系统类型的基础上的扩展。也就是说如果是系统类型，那么这两个字段的值就是一样的。所以这里我们就去用户类型id字段user_type_id。

（2）我们再看看max_length字段，它是指可以存储的最大字节数。这是怎么理解呢，我们知道在系统类型中会有varchar和nvarchar之类的类型，如果是varchar那么我们定义字段是输入的长度是多少那么max_length字段显示的就是多少。而如果是nvarchar的话，那么一个字符会占两个字节，所以当我们定义字段的时候输入的长度是100的话，那么实际的最大字节数是200，max_length字段显示的就是200.

下面我们通过sys.objects和sys.columns的联合即可拿到对应表的的列信息，代码如下

select
col.name as ColumnName,
col.max_length as DataLength,
col.is_nullable as IsNullable
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
where obj.name=@TableName

至于是用left join，还是right join或者inner join这里的结果都是一样（因为左边是唯一的而且是存在的，只要右边的有列存在那么三种的结果是一样的），但为了更加严谨一点这里我们还是用inner join好一点。

到这里我们其实拿到字段名、长度和是否为空的内容了，下面我再联合sys.types获取数据类型的名称。由之前的讨论结果，我们使用user_type_id这个字段进行联合。代码变成如下：

declare @TableName varchar(50)
set @TableName='TestTable'
select
col.name as ColumnName,
col.max_length as DataLength,
col.is_nullable as IsNullable,
t.name as DataType
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
left join sys.types t
on t.user_type_id=col.user_type_id
where obj.name=@TableName

下一步我们就开始读取描述，字段的描述是做为扩展属性存在扩展属性表中。属性以键值对的方式存在，这里就是name和value。我们添加字段时直接添加的描述是以MS_Description为名字的属性存在，所以我们在扩展属性表中拿字段描述的话要同时以这名字作为筛选条件。我们也可以通过字段右键属性中的扩展属性选项来添加其他名字的属性，这些数据也存在该表中，但不属于今天的讲解范围就不多说了。另外除了这个筛选条件之外，我们要确定具体到某个字段的描述，我们还得知道对应的表id和字段id，他们在扩展属性表中分别对应于major_id和minor_id。下面是添加了获取属性后的代码：

declare @TableName varchar(50)
set @TableName='TestTable'
select
col.name as ColumnName,
col.max_length as DataLength,
col.is_nullable as IsNullable,
t.name as DataType,
ep.value as Description
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
left join sys.types t
on t.user_type_id=col.user_type_id
left join sys.extended_properties ep
on ep.major_id=obj.object_id
and ep.minor_id=col.column_id
and ep.name='MS_Description'
where obj.name=@TableName

经过上面的努力我们要的信息都快全了，就剩下是否为主键，之所以把这个放在最后讲，是因为这个的获取没那么直接，它涉及到了sys.indexes和sys.index_columns两张表。在sys.indexes表中就有是否为主键的信息（对应字段为is_primary_key），但是它只跟表id（object_id）有直接的关联，要涉及到具体的字段我们还得联合sys.index_columns表。我们先通过sys.index_columns的object_id和column_id来筛选出具体的字段，然后在通过ic.index_id去sys.indexes来找到相应的记录来确定是否为主键。另外由于一字段可能有多个索引，所以我们还需要通过名字是否已PK_开头来做进一步的限定。修改后的代码如下：

select
col.name as ColumnName,
col.max_length as DataLength,
col.is_nullable as IsNullable,
t.name as DataType,
ep.value as Description,
(
    select top 1 ind.is_primary_key from sys.index_columns ic
    left join sys.indexes ind
    on ic.object_id=ind.object_id
    and ic.index_id=ind.index_id
    and ind.name like 'PK_%'
    where ic.object_id=obj.object_id
    and ic.column_id=col.column_id
) as IsPrimaryKey
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
left join sys.types t
on t.user_type_id=col.user_type_id
left join sys.extended_properties ep
on ep.major_id=obj.object_id
and ep.minor_id=col.column_id
and ep.name='MS_Description'
where obj.name=@TableName

至此我们就拿到我们想要的所有信息了。

七、注意的一些问题

（1）第一个注意的问题如下（摘自官方网）：

在 SQL Server 的未来版本中，Microsoft 可能会通过在列列表的末尾添加列来扩充任何系统目录视图的定义。我们建议不要在生产代码中使用语法 SELECT * FROM sys.catalog_view_name，这是因为返回的列数可能会更改和中断应用程序。

（2）Microsoft 建议不要直接查询系统表。而是应该使用视图进行查询。

八、完整的代码示例

下面给出了上面三种方案完整代码：

目录视图的方式：

select
col.name as ColumnName,
col.max_length as DataLength,
col.is_nullable as IsNullable,
t.name as DataType,
ep.value as Description,
(
    select top 1 ind.is_primary_key from sys.index_columns ic
    left join sys.indexes ind
    on ic.object_id=ind.object_id
    and ic.index_id=ind.index_id
    and ind.name like 'PK_%'
    where ic.object_id=obj.object_id
    and ic.column_id=col.column_id
) as IsPrimaryKey
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
left join sys.types t
on t.user_type_id=col.user_type_id
left join sys.extended_properties ep
on ep.major_id=obj.object_id
and ep.minor_id=col.column_id
and ep.name='MS_Description'
where obj.name=@TableName

信息架构视图的方式：

select
col.COLUMN_NAME as ColumnName,
col.DATA_TYPE as DataType,
col.CHARACTER_OCTET_LENGTH as DataLength,
col.IS_NULLABLE as IsNullable,
ccu.CONSTRAINT_NAME as IsPrimaryKey,
de.value as Description
from INFORMATION_SCHEMA.COLUMNS col
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
on ccu.TABLE_NAME=col.TABLE_NAME
and ccu.COLUMN_NAME=col.COLUMN_NAME
and ccu.CONSTRAINT_NAME like 'PK_%'
left join ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @TableName,'column', default) as de
on col.COLUMN_NAME = de.objname COLLATE Chinese_PRC_CI_AS
where col.TABLE_NAME=@TableName

由于信息架构视图里面没有存储扩展属性的相应视图，我们通过fn_listextendedproperty函数来获取，具体该函数的作用和使用可以百度之，这里就不多说了。

兼容视图的方式：

select
col.name as ColumnName,
col.isnullable as IsNullable,
col.length as DataLength,
tp.name as DataType,
ep.value as Descript,
(
    select count(*) from sys.sysobjects
    where parent_obj=obj.id
    and name=(
        select top 1 name from sys.sysindexes ind
        inner join sys.sysindexkeys indkey
        on ind.indid=indkey.indid
        and indkey.colid=col.colid
        and indkey.id=obj.id
        where ind.id=obj.id
        and ind.name like 'PK_%'
    )
) as IsPrimaryKey
from sys.sysobjects obj
inner join sys.syscolumns col
on obj.id = col.id
left join sys.systypes tp
on col.xtype=tp.xusertype
left join sys.extended_properties ep
on ep.major_id=obj.id
and ep.minor_id=col.colid
and ep.name='MS_Description'
where obj.name=@TableName

说明：以上各种方案取出来的一些字段信息的类型没有统一一致，也没有将相应的null转为为0之类的，在使用中在根据自己的需要来添加一些判断转为就是了。如信息架构视图的方式中返回的IsPrimaryKey是以PK_XXX之类的信息存在。而目录视图中是以1和0来表示，不是的情况还可能为null，使用时可以将null也先转换为0.

最后，以上如果有不对的地方或者因为疏忽没考虑不周到的地方或者有可以优化的地方欢迎指出和纠正。

博主补充一个IsIdentity标志：

select
col.name as ColumnName,
col.isnullable as IsNullable,
col.length as DataLength,
tp.name as DataType,
ep.value as Descript,
(
    select count(*) from sys.sysobjects
    where parent_obj=obj.id
    and name=(
        select top 1 name from sys.sysindexes ind
        inner join sys.sysindexkeys indkey
        on ind.indid=indkey.indid
        and indkey.colid=col.colid
        and indkey.id=obj.id
        where ind.id=obj.id
        and ind.name like 'PK_%'
    )
) as IsPrimaryKey
,
COLUMNPROPERTY(col.id,col.name,'IsIdentity') as IsIdentity
from sys.sysobjects obj
inner join sys.syscolumns col
on obj.id = col.id
left join sys.systypes tp
on col.xtype=tp.xusertype
left join sys.extended_properties ep
on ep.major_id=obj.id
and ep.minor_id=col.colid
and ep.name='MS_Description'
where obj.name=@TableName