Name of Primary Key columns on a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Name of Primary Key columns on a table

Author

Topic

ourspt
Starting Member

33 Posts

Posted - 2005-05-16 : 08:47:38

Hi,

I would like to know the way to identify the name of the primary key column(s) on a table (whether single column primary key or multi column primary key) programmatically in SQL Server (looking at something like sysobjects, syscolumns or whatever).

Thanks in advance
ourspt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-16 : 09:01:22

Is this?

sp_pkeys 'Table'

Madhivanan

Failing to plan is Planning to fail

ourspt
Starting Member

33 Posts

Posted - 2005-05-16 : 09:31:26

quote:
Originally posted by madhivanan

Is this?

sp_pkeys 'Table'

Madhivanan

Failing to plan is Planning to fail

Dear Madhivanan,

Actually, I need to use this within an SP. I will supply the table name and would like to open a cursor to capture the names of the primary key columns for that table. And then, within the cursor, I would do some processing for each priamry key column. But because this is an SP, I am not able to open a cursor with this output (i.e, I am not able to say 'Declare cursor PrimaryKeycursor CURSOR for sp_pkeys('Orders')
)
as it gives a syntax error. Instead, if I could have a select statement to achieve the same results as sp_pkeys, that would be great.

Hope you understood. Please let me know if questions.

Thanks in advance
ourspt

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-16 : 09:39:42

1. You dont need a cursor.
2. Select the sp_pkeys into a temp table, then process them with a set based solution.

If you dont want to do that, then simply script out the sp_pkeys proc and use the code in your own proc.

*need more coffee*

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-16 : 09:54:54

INFORMATION_SCHEMA.TABLE_CONSTRAINTS
has this information.

columns...

SELECT
	tc.TABLE_SCHEMA
	,tc.TABLE_NAME
	,tc.CONSTRAINT_NAME
	,kcu.COLUMN_NAME
	,kcu.ORDINAL_POSITION
FROM
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
		ON TC.CONSTRAINT_CATALOG =KCU.CONSTRAINT_CATALOG
		AND TC.CONSTRAINT_SCHEMA =KCU.CONSTRAINT_SCHEMA
		AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE
	tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
	1,2,3,4,5

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources