Primary Keys – Compare the Column Name with the Table Name

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;