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.
Lowell