Primary Keys – Compare the Column Name with the Table Name

Posted: October 16, 2009 in Primary Key, SQL 2005, TSQL

This script looks at all the Primary Key Indexes in a database and pulls back the name of the column the key is on. It checks where there is a single column key. It then recommends a name for the column of tableNameID – columns which already match this are filtered out.
I have written this because I have a bad habit of naming surrogate Primary Key columns PKID.
In most cases the column which doesn’t match the name formulae it is because it already had a good name – like serverName in my server audit table.

select si.name as IndexName,
so.name as TableName,
sc.name as columnName,
so.name + ‘Id’ as recommendName
from sys.indexes as si
Inner Join sys.objects as so
on si.object_id = so.object_id
Inner Join sys.index_columns as ic
on si.object_id = ic.object_id AND si.index_id = ic.index_id
Inner Join sys.columns as sc
on ic.object_id = sc.object_id and ic.index_column_id = sc.column_id
where si.is_Primary_key = 1
AND 1 = (SELECT count(object_id)
FROM sys.index_columns icInner
WHERE si.object_id = icInner.object_id AND si.index_id = icInner.index_id)
AND so.name + ‘id’ <> sc.name;
Advertisements

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