Find Missing Foreign Keys

I was trying to identify columns in a database that needed foreign keys. This script will return column names that appear more than once in a database and do not have foreign keys referring to them. The script only looks at numeric columns, and excludes some column names that I know won’t have foreign keys.

************************************************************************************************

— Find multiple instances of the same column name where the
— column isn’t part of a foreign key.
— May identify columns that need a FK created.

declare @ColumnList as table (
TableName varchar(128) not null,
ColumnName varchar(128) not null,
HasForeignKey bit not null
)

— Find all column names that occur more than once.
— Exclude archive and staging tables.
insert into @ColumnList(TableName, ColumnName, HasForeignKey)
select t.name as TableName, c.name as ColumnName,
 case when f1.parent_object_id is not null then 1
 when f2.referenced_object_id is not null then 1
 else 0 end as HasForeignKey
from sys.tables as t
join sys.columns as c
 on c.object_id = t.object_id
join sys.types as y
 on c.system_type_id = y.system_type_id
left join sys.foreign_key_columns as f1
 on f1.parent_object_id = t.object_id
 and f1.parent_column_id = c.column_id
left join sys.foreign_key_columns as f2
 on f2.referenced_object_id = t.object_id
 and f2.referenced_column_id = c.column_id
where t.is_ms_shipped = 0 and
 y.name in (‘bigint’, ‘int’, ‘smallint’, ‘tinyint’, ‘uniqueidentifier’) and
 c.name not in (‘DisplayOrder’, ‘MaxLength’) select TableName, ColumnName
from @ColumnList
where HasForeignKey = 0 and
ColumnName in (
select ColumnName
from @ColumnList
group by ColumnName
having count(*) > 1
)
order by ColumnName, TableName

go

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

%d bloggers like this: