Primary Keys - SQL Server Forums

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 Administration (2000)

Primary Keys

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-22 : 09:14:24

Steve writes "Hi,
the sp_pkeys stored procedure returns the primary key for one table.

Is there a stored procedure that returns the primary keys on all user tables?

Thanks for the help

Steve"

Jay99

468 Posts

Posted - 2002-03-22 : 09:56:14

search this site for the undocumented sp_msforeachtable

Jay
<O>

cyc
Starting Member

1 Post

Posted - 2002-03-22 : 10:37:40

Try this

SELECT NAME AS 'Table name' FROM SYSOBJECTS
WHERE NAME IN
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'PK%')
AND XTYPE ='U'
ORDER BY NAME

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-22 : 11:11:12

That will work UNLESS you create your primary keys with a name that doesn't begin with "PK". This query should get all of them for you:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources