SP_PKEYS

Last Post 13 Oct 2011 11:07 PM by Linu Varghese. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
g8r
New Member
New Member

--
12 Jul 2005 11:19 AM
Does anyone know a way to determine the primary key column names on a table by querying the system tables without using sp_pkeys?

We can't seem to link sysobjects and syscolumns. If we have to use sp_pkeys, we don't know how to iterate over the result set to get the column name(s).

Thanks!
mwesch
New Member
New Member

--
12 Jul 2005 03:08 PM
Instead of querying the system tables you should use the information_schema views.

select a.table_name, a.constraint_name, b.column_name
from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b
on a.constraint_name = b.constraint_name
where a.constraint_type = 'PRIMARY KEY'
order by a.table_name
SQLUSA
New Member
New Member

--
13 Jul 2005 01:31 AM
>we don't know how to iterate over the result set to get the column name(s).

What do you mean? sysobjects and syscolumns are just regular tables.

Kalman Toth, SQL Guru
http://ww.sqlsusa.com/encryption/

nosepicker
New Member
New Member

--
13 Jul 2005 09:30 AM
But that will miss out any user-defined primary keys that do not start with "pk" (like primary keys created with the alter table command).
g8r
New Member
New Member

--
13 Jul 2005 02:19 PM
Thanks to all for the input. I used rm's solution as is was very straightforward. I'm not sure about the 'alter table' being a problem, but for us this time it was not. Thanks again to rm.
ScottPletcher
New Member
New Member

--
14 Jul 2005 01:35 PM
This method does not depend on the primary key constraint name in any way

SELECT LEFT(OBJECT_NAME(ixk.id), 50) AS [Table Name],
LEFT(COL_NAME(ixk.id, ixk.colid), 40) AS [Column Name],
ixk.KeyNo
FROM sysobjects obj WITH (NOLOCK) --pk constraint
INNER JOIN sysobjects obj2 WITH (NOLOCK) ON obj.parent_obj = obj2.id --table w/ pk constraint
INNER JOIN sysindexkeys ixk WITH (NOLOCK) ON obj2.id = ixk.id --col(s) in constraint
WHERE obj.xtype = 'PK' AND obj2.xtype = 'U' --user tables only

Linu Varghese
New Member
New Member

--
13 Oct 2011 11:07 PM
 

SELECT     CCU.COLUMN_NAME
FROM         INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN
                      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG AND 
                      TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE='PRIMARY KEY'
WHERE     (TC.TABLE_NAME = 'YOURTABLENAME')





Acceptable Use Policy
---