Query for Primary and Other Candidate Keys

A primary key is simply a candidate key labelled as the ‘primary’ (see more in A Key to Keys), and they are enforced by the same mechanism in Sql Server – the unique index. We can query the system tables to get a quick list of these.

 

select	t.name as TableName,
	i.name as IndexName,
	i.is_primary_key,
	c.name as ColumnName,
	c.is_nullable,
	i.type_desc
from	sys.tables t
	inner join sys.indexes i
		on i.object_id = t.object_id
	inner join sys.index_columns ic
		on ic.object_id = i.object_id
		and ic.index_id = i.index_id
	inner join sys.columns c
		on c.object_id = t.object_id
		and c.column_id = ic.column_id
where	i.is_unique = 1
order by
	t.name asc,
	i.is_primary_key desc,
	i.name asc,
	c.name asc;

Here’s an example table:

Query or Primary and Candidate Keys Example Table

…and the results of the query:

Query for Primary and Candidate Keys Example Results

You might notice that the PK is not clustered. In Sql Server any index can be clustered, it does not have to be the PK and it does not have to be unique.

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