获取SqlServer2005表结构(字段，主键，外键，递增，描述)

快来打我* 2022-08-19 04:45 153阅读 0赞

转自：http://www.cnblogs.com/eflylab/archive/2008/06/23/1227838.html

1.获取表的基本字段属性

\-- 获取SqlServer中表结构    
 SELECT  syscolumns.name,systypes.name,syscolumns.isnullable,  
syscolumns.length  
 FROM  syscolumns, systypes  
 WHERE  syscolumns.xusertype  =  systypes.xusertype  
 AND  syscolumns.id  =  object\_id ( ' 你的表名 ' )

运行效果  
![2008-06-23_002758.jpg][]  
2.如果还想要获取字段的描述信息则

![复制代码][copycode.gif]

\-- 获取SqlServer中表结构 主键，及描述   
 declare  @table\_name  as  varchar ( max )  
 set  @table\_name  =  ' 你的表名 '   
 select  sys.columns.name, sys.types.name, sys.columns.max\_length, sys.columns.is\_nullable,  
( select  count ( \* )  from  sys.identity\_columns  where  sys.identity\_columns. object\_id  =  sys.columns. object\_id  and  sys.columns.column\_id  =  sys.identity\_columns.column\_id)  as  is\_identity ,  
( select  value  from  sys.extended\_properties  where  sys.extended\_properties.major\_id  =  sys.columns. object\_id  and  sys.extended\_properties.minor\_id  =  sys.columns.column\_id)  as  description  
 from  sys.columns, sys.tables, sys.types  where  sys.columns. object\_id  =  sys.tables. object\_id  and  sys.columns.system\_type\_id = sys.types.system\_type\_id  and  sys.tables.name = @table\_name  order  by  sys.columns.column\_id

![复制代码][copycode.gif]

运行效果  
![2008-06-23_003008.jpg][]  
3.单独查询表的递增字段

\-- 单独查询表递增字段   
 select  \[ name \]  from  syscolumns  where   
id = object\_id (N ' 你的表名 ' )  and  COLUMNPROPERTY (id,name, ' IsIdentity ' ) = **1**

运行效果  
![2008-06-23_003641.jpg][]  
4.获取表的主外键

\-- 获取表主外键约束   
 exec  sp\_helpconstraint  ' 你的表名 '  ;

运行效果  
![2008-06-23_003431.jpg][]

[2008-06-23_002758.jpg]: /images/20220731/6f01f77985e14ead8308e0f128bf8e08.png
[copycode.gif]: /images/20220731/f732c0323aed42e68f55a0a9d82324d8.png
[2008-06-23_003008.jpg]: /images/20220731/20d2889be1ca47a08d350a0f939e4e35.png
[2008-06-23_003641.jpg]: /images/20220731/efdbec85df11446ab455c3ef4261ad38.png
[2008-06-23_003431.jpg]: /images/20220731/cc812f14130d4a3c8868efe727466caf.png