sqlserver2000通过系统表获得表结构信息

1.用户表信息

select  name  from  sysobjects
where   objectproperty (id, ' IsUserTable ' ) = 1

2.表的列信息
列信息包括列名,数据类型,长度,精度,小数位数,是否允许为空,默认值 

select  a.name  as  columnname,b.name  as  datatype,a.length,a.prec,a.scale,a.isnullable,c. text   as   default
from  syscolumns a  inner   join  systypes b  on  (a.xtype = b.xtype)
left   join  syscomments c  on  a.cdefault = c.id
where  a.id = object_id ( ' TableName ' )
order   by  colorder

3.表的主键信息 

declare   @objectid   int
set   @objectid = object_id ( ' TableName ' )
select    
col_name ( @objectid ,k.colid) columnname
from   sysobjects o
inner   join  sysindexes i  on  i.name = o.name 
inner   join  sysindexkeys k  on  k.indid = i.indid
where  o.xtype  =   ' PK '   and  o.parent_obj = @objectid   and  k.id = @objectid

 

转载于:https://www.cnblogs.com/ustc9702/archive/2007/05/22/755379.html