如何从系统表中查到一个主键对应的字段
select * from sysobjects where xtype= 'pk ' and parentID=(select id from sysobjects where xtype= 'u ' and name= '表名 ')
通过这个语句能够查到某个表的主键object，但是如何知道这个主键object对应是该表的哪个字段呢？
[解决办法]
select e.name as '主键字段名 '
from sysobjects a,sysindexes b,sysindexkeys d,syscolumns e
where a.xtype= 'pk ' and parent_obj=(select id from sysobjects where xtype= 'u ' and name= '表名 ')
and a.name=b.name and b.indid=d.indid and b.id=d.id and d.id=e.id and d.colid=e.colid
[解决办法]
declare @table_name varchar(100)
set @table_name= 'table_Pqs '
select
col_name(object_id(@table_name),colid) as '主键字段 '
from sysobjects as o
inner join sysindexes as i on i.name=o.name
inner join sysindexkeys as k on k.indid=i.indid and k.id=object_id(@table_name)
where
o.xtype = 'PK ' and parent_obj=object_id(@table_name)
