select * from sysobjects where xtype='pk' and parentID=(select id from sysobjects where xtype='u' and name='表名')通过这个语句能够查到某个表的主键object,但是如何知道这个主键object对应是该表的哪个字段呢?
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO IF EXISTS (SELECT name FROM sysobjects WHERE name = N'sp_get_pkeys' AND type = 'P') DROP PROCEDURE sp_get_pkeys GO Create procedure sp_get_pkeys (@table_name sysname) as declare @table_id int select @table_id = object_id(@table_name) select TABLE_NAME = convert(sysname,o.name), COLUMN_NAME = convert(sysname,c.name) from sysindexes i, syscolumns c, sysobjects o where o.id = @table_id--object_id(@table_name) and o.id = c.id and o.id = i.id and (i.status & 0x800) = 0x800 and (c.name = index_col (@table_name, i.indid, 1) or c.name = index_col (@table_name, i.indid, 2) or c.name = index_col (@table_name, i.indid, 3) or c.name = index_col (@table_name, i.indid, 4) or c.name = index_col (@table_name, i.indid, 5) or c.name = index_col (@table_name, i.indid, 6) or c.name = index_col (@table_name, i.indid, 7) or c.name = index_col (@table_name, i.indid, 8) or c.name = index_col (@table_name, i.indid, 9) or c.name = index_col (@table_name, i.indid, 10) or c.name = index_col (@table_name, i.indid, 11) or c.name = index_col (@table_name, i.indid, 12) or c.name = index_col (@table_name, i.indid, 13) or c.name = index_col (@table_name, i.indid, 14) or c.name = index_col (@table_name, i.indid, 15) or c.name = index_col (@table_name, i.indid, 16) ) order by 1, 2GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ------------------------------------------------------ 这个是我根据那个自己改的. 不过,我想还是学会方法更重要.
sp命令不返回结果集的,另外无法访问master库
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)
select sc.name as clmname,st.name as type, sc.length ,sc.xprec,sc.xscale,sc.isnullable ,(case when sc.name in (select e.name 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 ) then 1 else 0 end) as isPK from syscolumns sc, systypes st where id=(select id from sysobjects where xtype='u' and name='表名') and sc.xusertype=st.xusertype感谢楼上各位的帮助
master..sp_helptext sp_pkeys
看看sp_pkeys然后自己写吧
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_get_pkeys'
AND type = 'P')
DROP PROCEDURE sp_get_pkeys
GO
Create procedure sp_get_pkeys (@table_name sysname)
as
declare @table_id int
select @table_id = object_id(@table_name) select
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name) from
sysindexes i, syscolumns c, sysobjects o
where
o.id = @table_id--object_id(@table_name)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and (c.name = index_col (@table_name, i.indid, 1) or
c.name = index_col (@table_name, i.indid, 2) or
c.name = index_col (@table_name, i.indid, 3) or
c.name = index_col (@table_name, i.indid, 4) or
c.name = index_col (@table_name, i.indid, 5) or
c.name = index_col (@table_name, i.indid, 6) or
c.name = index_col (@table_name, i.indid, 7) or
c.name = index_col (@table_name, i.indid, 8) or
c.name = index_col (@table_name, i.indid, 9) or
c.name = index_col (@table_name, i.indid, 10) or
c.name = index_col (@table_name, i.indid, 11) or
c.name = index_col (@table_name, i.indid, 12) or
c.name = index_col (@table_name, i.indid, 13) or
c.name = index_col (@table_name, i.indid, 14) or
c.name = index_col (@table_name, i.indid, 15) or
c.name = index_col (@table_name, i.indid, 16)
)
order by 1, 2GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------
这个是我根据那个自己改的.
不过,我想还是学会方法更重要.
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
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)
,(case when sc.name in (select e.name
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
) then 1 else 0 end) as isPK
from syscolumns sc, systypes st
where id=(select id from sysobjects where xtype='u' and name='表名')
and sc.xusertype=st.xusertype感谢楼上各位的帮助