"Raghuraman" <ra************@rediffmail.com> wrote in message
news:66*************************@posting.google.co m...
Hai ,
I am in the situation to find the p.key field from a table.So i use
sp_pkeys 'table' .It works nice.
But i want to select the pkey field alone instead of the rest of the
informations supplied by the sp.
How to get the P.key field name alone from a table .Or If u tell the
way to store the results of sp_pkeys 'table' into a table also ok to
me.
With Regards
Raghu
You didn't mention which version of SQL Server you have, but in 7/2000, you
can use the INFORMATION_SCHEMA views:
select
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
kcu.COLUMN_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and
tc.TABLE_SCHEMA = 'dbo' and
tc.TABLE_NAME = 'MyTable'
Alternatively, you can put the output of sp_pkeys into a table:
create table #keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
go
insert into #keys
exec sp_pkeys 'MyTable'
go
Simon
