--Missing_And_Implied_Foreign_Keys.sql
if object_id('tempdb..#FKFINDER') Is not null 
 drop table #FKFINDER


if object_id('tempdb..#CurrFKS') Is not null 
 drop table #CurrFKS


SELECT 
  CONVERT(VARCHAR(500),'') AS  STATUS ,
  SYSOBJECTS.NAME             AS TBLNAME,
  SYSOBJECTS.ID               AS TBLID,
  SYSCOLUMNS.NAME             AS COLNAME,
  TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
  INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
--tables we know we can exclude
  AND SYSOBJECTS.NAME NOT IN('CMCONT','CMMLADDR','CMADDR')
  AND TYPE_NAME(SYSCOLUMNS.XTYPE)='INT'
  AND SYSCOLUMNS.NAME LIKE '%TBLKEY' --or %ID or some convention?
ORDER BY TBLNAME,COLNAME


SELECT 
OBJECT_NAME(constid)    AS CONSTRAINTNAME,
OBJECT_NAME(rkeyid)     AS REFTABLE,
COL_NAME(rkeyid,rkey)   AS REFCOLUMN,
OBJECT_NAME(fkeyid)     AS FKTABLE,
COL_NAME(fkeyid,fkey)   AS FKCOLUMN,
' ALTER TABLE '      + OBJECT_NAME(fkeyid)  
+ ' ADD CONSTRAINT ' + OBJECT_NAME(constid) 
+ ' FOREIGN KEY ('   + COL_NAME(fkeyid,fkey)
+ ') REFERENCES '    + OBJECT_NAME(rkeyid) 
+'(' + COL_NAME(rkeyid,rkey) + ')' AS FKSQL
Into #CurrFKS
from sysforeignkeys

--mark all the PK's
UPDATE #FKFINDER
SET STATUS = 'PK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.REFTABLE
  AND #FKFINDER.COLNAME = #CURRFKS.REFCOLUMN

UPDATE #FKFINDER
SET STATUS = 'PK'
FROM(
      SELECT OBJECT_NAME(ID) AS PKTABLE,
        NAME as PKCOLUMN,
      * FROM SYSCOLUMNS
      WHERE SYSCOLUMNS.NAME LIKE '%TBLKEY' --or %ID or some convention?
        AND COLID=1) X
WHERE #FKFINDER.TBLNAME = X.PKTABLE
  AND #FKFINDER.COLNAME = X.PKCOLUMN
 
--mark all the curr FK's
UPDATE #FKFINDER
SET STATUS = 'FK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.FKTABLE
  AND #FKFINDER.COLNAME = #CURRFKS.FKCOLUMN
--fix specific to mail columns
UPDATE #FKFINDER
SET STATUS = 'FK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.FKTABLE
  AND #FKFINDER.COLNAME = #CURRFKS.FKCOLUMN

--here's a best guess as to what it might point to:
UPDATE #FKFINDER
--SET STATUS=X.TBLNAME + '.' + X.COLNAME
SET STATUS = 'ALTER TABLE ' + #FKFINDER.TBLNAME + ' ADD FOREIGN KEY (' + #FKFINDER.COLNAME + ') REFERENCES ' + X.TBLNAME + '(' + X.COLNAME + ')'
FROM (SELECT * FROM #FKFINDER WHERE STATUS='PK') X
WHERE #FKFINDER.STATUS NOT IN('PK','FK')
AND #FKFINDER.COLNAME = X.COLNAME

--fix against columns starting with 'MAIL'
UPDATE #FKFINDER
--SET STATUS=X.TBLNAME + '.' + X.COLNAME
SET STATUS = 'ALTER TABLE ' + #FKFINDER.TBLNAME + ' ADD FOREIGN KEY (' + #FKFINDER.COLNAME + ') REFERENCES ' + X.TBLNAME + '(' + X.COLNAME + ')'
FROM (SELECT * FROM #FKFINDER WHERE STATUS='PK') X
WHERE #FKFINDER.STATUS NOT IN('PK','FK')
AND REPLACE(#FKFINDER.COLNAME,'MAIL','') = X.COLNAME

--this is a list of all columns that end in "TBLKEY", which implies a FK link according to our schema, but does nto have a FK using the table/column.

--not definitive, but a good guess/good start need to examine carefully
SELECT * 
FROM #FKFINDER 
WHERE STATUS NOT IN('PK','FK') 
AND STATUS LIKE 'ALTER%'
--and status like '%GMPROJ%' --testing for items for a specific table i THINK had missing FK
ORDER BY TBLNAME,COLNAME