How to find out the missing foreign keys

I got an assignment to find out the foreign key missing in our database. Our current practices is the Primary Column Name in Master table and the child tables are same. I have attached the example how we use to create the tables and how to find out the missing foreign keys. This Scripts can be changed according to your project requirement...

------------------------------------------------------------------------------------------------------------------------------------
USE tempdb
GO

------------------------------------------------------------------------------------------------------------------------------------
-- Just for testing created 2 tables

    CREATE TABLE TB_ORDER_MASTER (ORDER_MASTER_ID INT CONSTRAINT PK_ORDER_MASTER_ID PRIMARY KEY, ORDER_NAME VARCHAR(100))
    GO
    CREATE TABLE TB_ORDER_DETAILS (ORDER_DETAILS_ID INT CONSTRAINT PK_ORDER_DETAILS_ID PRIMARY KEY, ORDER_MASTER_ID INT, ORDER_DATE DATETIME)
    GO
------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------
-- Get the Tables which Contains Primary Key

    IF OBJECT_ID('tempdb..#temp_PK','U') IS NOT NULL DROP TABLE tempdb.dbo.#temp_PK
    SELECT  PK.name AS CONSTRAINT_NAME,
            TBL.name AS TABLE_NAME,
            COL.name AS COLUMN_NAME
    into #temp_PK
    FROM sys.key_constraints as PK
        INNER JOIN sys.tables as TBL
                ON TBL.object_id = PK.parent_object_id
        INNER JOIN sys.index_columns as IDXCOL
                ON IDXCOL.object_id = TBL.object_id
                AND IDXCOL.index_id = PK.unique_index_id
        INNER JOIN sys.columns as COL
                ON COL.object_id = TBL.object_id
                AND COL.column_id = IDXCOL.column_id
    WHERE
        PK.type = 'PK'
      
    select * from #temp_PK  

/*
--Output

CONSTRAINT_NAME        TABLE_NAME            COLUMN_NAME
PK_ORDER_MASTER_ID    TB_ORDER_MASTER        ORDER_MASTER_ID
PK_ORDER_DETAILS_ID    TB_ORDER_DETAILS    ORDER_DETAILS_ID

*/
------------------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------------------------------

-- Get the Foreig Key columns and details.
    IF OBJECT_ID('tempdb..#temp_FK','U') IS NOT NULL DROP TABLE tempdb.dbo.#temp_FK
    SELECT
            TBL.name AS TABLE_NAME  ,
            COL.name AS COLUMN_NAME
    INTO    #temp_FK
    FROM sys.foreign_key_columns FKC
        INNER JOIN sys.sysobjects OBJ
                ON FKC.constraint_object_id=OBJ.id
        INNER JOIN sys.sysobjects TBL
                ON FKC.parent_object_id=TBL.id
        INNER JOIN sys.all_columns COL
                ON FKC.parent_object_id=COL.object_id /* ID of the object to which this column belongs.*/
                AND FKC.parent_column_id=COL.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/

    select * from #temp_FK

    /*
    --Output
    TABLE_NAME        COLUMN_NAME  
    NULL            NULL
    */
------------------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------------------------------
-- GET THE RESULT FOR MISSING FOREIGN KEYS
SELECT    ZZZ.COLUMN_NAME MISSING_FK_COLUMN_NAME, ZZZ.TABLE_NAME MISSING_FK_TABLE_NAME,
        PK.TABLE_NAME PK_TABLE_NAME
  FROM
        (
            SELECT *
              FROM
                (
                         SELECT CLM.COLUMN_NAME, CLM.TABLE_NAME
                           FROM INFORMATION_SCHEMA.COLUMNS CLM JOIN
                                #TEMP_PK PK
                                    ON CLM.COLUMN_NAME = PK.COLUMN_NAME -- Join the PK Column to get all the columns with named as Primary Key
                  
                    -- To Avoid the Actual Primary Keys
                    EXCEPT
                  
                        SELECT COLUMN_NAME, TABLE_NAME
                          FROM #TEMP_PK
                ) X

            EXCEPT

             SELECT COLUMN_NAME, TABLE_NAME
               FROM #TEMP_FK
        ) ZZZ JOIN
          #TEMP_PK PK
            ON ZZZ.COLUMN_NAME = PK.COLUMN_NAME

    ORDER BY ZZZ.COLUMN_NAME, ZZZ.TABLE_NAME


/*
--Output
MISSING_FK_COLUMN_NAME    MISSING_FK_TABLE_NAME    PK_TABLE_NAME
ORDER_MASTER_ID            TB_ORDER_DETAILS        TB_ORDER_MASTER
*/
------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------
-- Now we got the list and create the Foreign Keys
ALTER TABLE TB_ORDER_DETAILS ADD CONSTRAINT FK_ORD_DET_X_ORDER_MASTER_ID_X_ORD_MAS FOREIGN KEY(ORDER_MASTER_ID)
 REFERENCES TB_ORDER_MASTER(ORDER_MASTER_ID)
GO

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE TB_ORDER_MASTER
GO
DROP TABLE TB_ORDER_DETAILS
GO

------------------------------------------------------------------------------------------------------------------------------------