SQL Server: Show all the PRIMARY KEYS for a TABLE

For SQL Server 2005 +

[sourcecode language="SQL"]
DECLARE
  @tableName nvarchar(MAX)
SET
  @tableName = 'tableName' -- (1)
SELECT
  tc.TABLE_NAME,
  tc.CONSTRAINT_NAME,
  kcu.COLUMN_NAME,
  kcu.ORDINAL_POSITION
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE
  tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND
  tc.TABLE_NAME = @tableName OR @tableName IS NULL
ORDER BY
  tc.TABLE_NAME,
  kcu.ORDINAL_POSITION
[/sourcecode]

Notes:

  • Change line (1) to specify the table name. Removing this line will list PRIMARY KEYS for all TABLES.
Be Sociable, Share!
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *