October 17, 2002 at 9:54 am
What's the most authoritative place to find information on accessing the system tables. The BOL is pretty sparse.
My current question is how can I find all declared primary keys. I "invented" one way by reading BOL:
select cl.name
from sysconstraints c
inner join sysobjects o on o.id = c.constid
inner join sysindexes i on i.id=Object_Id(@Table) and i.name = o.name
inner join sysindexkeys k on k.id=Object_id(@Table) and k.indid=i.indid
inner join syscolumns cl on cl.id=Object_id(@Table) and cl.colid=k.colid
where c.id = Object_Id(@Table) and c.status & 1 = 1
I found another way in some of the posted scripts here, and confess to understanding only part of it:
select t_obj.name, col.name
from syscolumns col
inner join sysobjects c_obj on c_obj.uid=user_id() and c_obj.xtype='PK'
inner join sysobjects t_obj on t_obj.id=c_obj.parent_obj and t_obj.xtype='U' and t_obj.id=col.id
inner join sysindexes i on i.id=t_obj.id and i.name=c_obj.name
inner join master.dbo.spt_values v on v.number>0 and v.number<=i.keycnt and v.type='P'
where col.name=index_col(t_obj.name,i.indid,v.number) and col.id=object_id(@Table)
And the more I look the more I see other variations.
I'm using the latter as it seems to work (in fact both the above give the same results on the tables on which I've tested it), and figure someone else HAD to know more than I feel like I do about these connections.
Is there a "right" way for this?
And how does one know?
October 17, 2002 at 10:01 am
What about sp_pkeys.
October 17, 2002 at 10:09 am
sp_pkeys -- gesh... blush... thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply