I've got a script i call missing and implied foreign keys that i built over the years.

it's basically a peel the onion kind of attack, which kind of assumes some naming conventions are in place(the FK column exactly equals or contains the PK column name, maybe with a prefix/suffix.

find all PK's and UQ's, and then any columns that equal or contain, but are not featured in the current FK's

--current PK/UQ columns

;WITH CurrFKS

AS (SELECT Object_name(constid) AS constraintname,

OBJECT_SCHEMA_NAME(rkeyid) AS refschema,

Object_name(rkeyid) AS reftable,

COL_NAME(rkeyid, rkey) AS refcolumn,

OBJECT_SCHEMA_NAME(rkeyid) AS fkschema,

Object_name(fkeyid) AS fktable,

COL_NAME(fkeyid, fkey) AS fkcolumn,

' ALTER TABLE '

+ Object_name(fkeyid)

+ ' ADD CONSTRAINT [PLACEHOLDER]'

+ ' FOREIGN KEY ('

+ COL_NAME(fkeyid, fkey)

+ ') REFERENCES '

+ Object_name(rkeyid) + '('

+ COL_NAME(rkeyid, rkey) + ')' AS fksql

FROM sysforeignkeys

),

MyCTE

AS (SELECT Object_schema_name(colz.object_id) AS FKSchemaName,

Object_name(colz.object_id) AS FKTableName,

colz.NAME AS FKColumnName,

colz.column_id AS FKcolumn_id,

CurrentReferenceCandidates.*

FROM sys.columns colz

INNER JOIN (SELECT

idxz.object_id,

object_schema_name(idxz.object_id) As PKSchemaName,

object_name(idxz.object_id) As PKTableName,

pcolz.name As PKColumnName,

pcolz.column_id As PKColumnID,

idxz.is_primary_key ,

idxz.is_unique

from sys.indexes idxz

inner join sys.index_columns icolz

on idxz.object_id = icolz.object_id

inner join sys.columns pcolz

ON icolz.object_id = pcolz.object_id

AND icolz.column_id = pcolz.column_id

WHERE (idxz.is_primary_key = 1 OR idxz.is_unique = 1)

and object_schema_name(idxz.object_id) <> 'sys') CurrentReferenceCandidates

ON colz.NAME = CurrentReferenceCandidates.PKColumnName

----ON colz.name like '%' + CurrentReferenceCandidates.PKColumnName + '%'

AND colz.object_id != CurrentReferenceCandidates.object_id

WHERE Object_Schema_name(colz.object_id) != 'sys'

AND colz.object_id != CurrentReferenceCandidates.object_id

AND Object_schema_name(colz.object_id) = pkschemaname

AND colz.name = CurrentReferenceCandidates.PKColumnName --toggle below for second version

--AND colz.name like '%' + CurrentReferenceCandidates.PKColumnName + '%'

) SELECT ' ALTER TABLE ' + FKSchemaName + '.'

+ FKTableName + ' ADD CONSTRAINT [PLACEHOLDER]'

-- + FKTableName + '_' + FKColumnName + ']'

+ ' FOREIGN KEY (' + FKColumnName

+ ') REFERENCES ' + pkschemaname + '.'

+ PKTableName + '(' + PKColumnName + ')' AS fksql

FROM MYCTE

WHERE FKTableName NOT IN(SELECT NAME

FROM sys.views)

EXCEPT

SELECT FKSQL

FROM CurrFKS;

then i have variations of it, which assume someone didn't create primary keys in the first place.

two versions, one which assumes any identity columns should be a PK but the PK is missing, and the last version where i assume the first column in a table is the potential PK, and compare other tables based on the same naming convention assumption.