Design : Find and script out Missing Foreign Keys

Question

Design : Find and script out Missing Foreign Keys

MadAdmin

SSChampion

Points: 11261
More actions
January 22, 2016 at 4:52 am

#324201

Comments posted to this topic are about the item Design : Find and script out Missing Foreign Keys
Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rcerney 63319 SSC Veteran Points: 206 More actions · Answer 1

Great script! Just a note that this script as written will only work with 2012+ due to the concat function. It could be easily changed though if your database version is lower. This works on my 2008 r2 instances.

SELECT

( S.name + '.' + O.name ) BaseTable, C.name BaseColumn,

( S2.name + '.' + O2.name ) AS ReferenceTable,

( 'alter table ' + S.name + '.' + O.name + ' with check add constraint FK_' + O.name + '_' + O2.name + ' foreign key (' + C.name + ') references ' + S2.name + '.' + O2.name + '(' + IDC.name + ')' ) FKCreateStatement

FROM

sys.columns C

INNER JOIN sys.identity_columns IDC ON (

IDC.name = C.name OR C.name = OBJECT_NAME(IDC.object_id) + IDC.name

) AND C.object_id <> IDC.object_id AND C.is_identity = 0 --exlude Columns which are identities

INNER JOIN sys.objects O ON O.object_id = C.object_id AND O.is_ms_shipped = 0 AND O.type = 'u'

INNER JOIN sys.schemas S ON S.schema_id = O.schema_id

INNER JOIN sys.objects O2 ON O2.object_id = IDC.object_id AND O2.is_ms_shipped = 0 AND O2.type = 'u'

INNER JOIN sys.schemas S2 ON S2.schema_id = O2.schema_id

LEFT JOIN sys.foreign_key_columns FKC ON IDC.object_id = FKC.referenced_object_id AND FKC.referenced_column_id = IDC.column_id

INNER JOIN (

SELECT

I.object_id, IC.index_id

FROM

sys.index_columns IC

INNER JOIN sys.indexes I ON I.object_id = IC.object_id AND I.index_id = IC.index_id

WHERE

I.is_primary_key = 1

GROUP BY

I.object_id, IC.index_id

HAVING

COUNT(*) = 1

) SingleColumnPK ON IDC.object_id = SingleColumnPK.object_id

WHERE

FKC.referenced_object_id IS NULL AND C.name <> 'ID'

ORDER BY

1;

MadAdmin SSChampion Points: 11261 More actions · Answer 2

rcerney 63319 (2/1/2016)
Just a note that this script as written will only work with 2012+ due to the concat function. It could be easily changed though if your database version is lower. This works on my 2008 r2 instances.

Cool.

You can add the script and in title you can mention it is compatible with 2005+.

Great that you find it useful.

Take care.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]