Retreival of Primary Key of a table through SQL

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

SQL Server Development (2000)

Retreival of Primary Key of a table through SQL

Author

Topic

rana_8129
Starting Member

1 Post

Posted - 2004-08-16 : 01:55:48

I want to retreive the primary key of a table so that it can be used to find the mismatch between the records in two tables .The table names are given during runtime.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-16 : 02:23:15

You can find the PK of a table by looking at the system tables or the Information_Schema views. Check out BOL for more info on this.
When you've got the column name, you can build an SQL string along the lines of

SELECT columname FROM tablename

and execute it using the sp_executesql procedure.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-08-16 : 02:23:59

Take a look at these views:

INFORMATION_SCHEMA.TABLE_CONSTRAINTS
and
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

They might contain the info you need.

/A

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-16 : 07:59:56

Take a look at the sp_pkeys system stored procedure.

/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-08-17 : 05:41:35

I use this


SELECT	KCU.COLUMN_NAME
WHERE	INFORMATION_SCHEMA.table_constraints TC
	JOIN INFORMATION_SCHEMA.key_column_usage KCU
		 ON KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
		AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
		AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE	    TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
	AND TC.TABLE_NAME = 'MyTableName'
ORDER BY TC.TABLE_NAME, KCU.ordinal_position

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources