Help needed in sp_pkeys

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"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0402030621.2bb732b@.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|||
Hai Simon

Thanks for your code.

I am in Sql server 7.0 and i make use of it

Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!