How do I list all Primary Keys in a SQL Server 2000/2005 database?

June 4, 2008 at 11:18 pm Leave a comment

Select the database you wish to list primary keys from and run the following SQL query:

SELECT
kcu.TABLE_SCHEMA AS ‘Schema’,
kcu.TABLE_NAME AS ‘Table’,
kcu.CONSTRAINT_NAME AS ‘Constraint Name’,
kcu.COLUMN_NAME AS ‘Column’
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
tc.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY
kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME

After running this script you should get a table listing the Schema, Table, Constraint Name and the Column for each Primary Key specified.

The screenshot below shows what results you will get if you run it on the AdventureWorks database:

Advertisements

Entry filed under: SQL Scripts. Tags: , , , , , , , , .

How do I shrink/truncate the transaction log? How do I check the Default Server Collation (case-sensitivity) of a SQL Server installation?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Calendar

June 2008
M T W T F S S
    Jul »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Archives

Blog Stats

  • 32,558 hits

%d bloggers like this: