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