How to find Primary Key and Key columns for tables in SQL Server

Its a very regular question in many of the forums “How to identify the primary key and its key column information for tables in SQL Server“.

Method 1:

1. Select the table in SSMS – (Query window)
2. Press – ALT-F1

The limitation is that, the above method can be useful only for a single table. If you are looking for a list of tables, then you can refer the second method.

Method 2:

T-SQL Script to identify the Primary key and Key column information for all tables.


;WITH CTE 
AS
(
	SELECT A.TABLE_SCHEMA,A.TABLE_NAME,A.CONSTRAINT_NAME,B.COLUMN_NAME,B.ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME 
			AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
	WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
		--AND A.TABLE_NAME LIKE 'Tablename%' -- To filter for a particular table/name starts with
)
SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE A
					WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
					ORDER BY A.ORDINAL_POSITION
					FOR XML PATH ('') )
					, ' ', ',')
FROM CTE B

References:
You may refer the below link to get more information for a table.
Handy Table Info Script

Advertisements

3 thoughts on “How to find Primary Key and Key columns for tables in SQL Server

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