Home > Database > Mysql Tutorial > 如何获取SqlServer2005表结构(字段,主键,外键,递增,描述)_MySQL

如何获取SqlServer2005表结构(字段,主键,外键,递增,描述)_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-01 13:24:35
Original
927 people have browsed it

SQLServer2005

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

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

运行效果

2.如果还想要获取字段的描述信息则

--获取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

运行效果


3.单独查询表的递增字段

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

运行效果


4.获取表的主外键

--获取表主外键约束
exec sp_helpconstraint   '你的表名' ;

运行效果


bitsCN.com
Related labels:
how
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template