List potentially missing Foreign Keys

ListPotMissing_FKs

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo._______aaaaaaa') AND type in (N'U'))
DROP TABLE dbo._______aaaaaaa 
GO



CREATE TABLE dbo._______aaaaaaa 
(
	 POTFK_TABLE_NAME nvarchar(128) NULL
	,POTFK_COLUMN_NAME nvarchar(128) NULL 
	,POTFK_PK_Table nvarchar(128) NULL
	,POTFK_PK_Column nvarchar(128) NULL 
); 

GO 


INSERT INTO _______aaaaaaa 
( 
	 POTFK_TABLE_NAME 
	,POTFK_COLUMN_NAME 
	,POTFK_PK_Table 
	,POTFK_PK_Column 
) 
SELECT 
	 isc.TABLE_NAME 
	,isc.COLUMN_NAME 
	,PKview.PK_Table 
	,PKview.PK_Column 
FROM INFORMATION_SCHEMA.COLUMNS AS isc 

INNER JOIN 
	(
		SELECT 
			 ccu.TABLE_SCHEMA AS PK_Schema 
			,ccu.TABLE_NAME AS PK_Table 
			,ccu.COLUMN_NAME AS PK_Column
			--,ccu.CONSTRAINT_NAME 
			--,ic.ORDINAL_POSITION 
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

		LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
			ON tc.CONSTRAINT_NAME = ccu.Constraint_name 
			
		LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS ic
			ON ic.TABLE_SCHEMA = ccu.TABLE_SCHEMA
			AND ic.TABLE_NAME = ccu.TABLE_NAME
			AND ic.COLUMN_NAME = ccu.COLUMN_NAME 
			
		WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
		AND ccu.TABLE_NAME NOT LIKE 'T[_]ZO[_]%[_]DWG' 
		AND ccu.TABLE_NAME NOT IN 
		(
			 'sysdiagrams', 'dtproperties' 
			,'ELMAH_Error', 'T_COR_Error', 'T_DMS_Sessions', 'T_FMS_Dashboard_27082014' 
			,'T_Export_Raum', 'T_Export_Raum_History'
			
			,'T_Benutzerrechte' -- UID 
			,'T_Benutzergruppen' -- ID 
			,'T_LOG_ProzedurRun' --	ID
			,'T_AP_Ref_Mandant' -- MDT_ID
			
			,'T_AP_Gebaeude_SwissLife'
			,'T_AP_Gebaeude_Reichle'
			,'T_AP_Geschoss_Reichle'
			,'T_AP_Geschoss_Reichle'			
			,'T_AP_Ref_Kostenstelle_Raiffeisen'
			,'T_AP_Ref_Organisationseinheit_Raiffeisen'
			,'T_AP_Ref_Nutzungsart_Raiffeisen'
			,'T_AP_Ref_MobiliarBibliothek_Raiffeisen'
			
			,'T_SYS_Language_DayNames', 'T_SYS_Language_MonthNames', 'T_SYS_Language_Forms',  'T_SYS_Language_Forms_i18n', 'T_SYS_Language_Forms_i18n_Cust' 
			,'T_AP_Legenden', 'T_AP_StichtagVisualisierung', 'T_SYS_VisualisierungStichtag' 
			,'T_AP_LinkDWG', 'T_AP_DWGSAVE', 'T_AP_SHOW_BeilageMV', 'T_ZO_AP_SHOW_Geschoss_Reinigungsplanung' 
			,'T_ZO_Benutzer_Mandant', 'T_ZO_AP_Schluesselgruppe_Mandant', 'T_ZO_AP_Zylinder_Mandant', 'T_ZO_REM_AP_Planung_Ref_Raum' 
			,'ZZ_TIMESTAMPS', '__Import_AP_Objekte' 
		) -- End not in 
		
	) AS PKview 
	ON isc.COLUMN_NAME	LIKE ('%[_]' + REPLACE(PKview.PK_Column, '_', '\_')) ESCAPE '\' 
	--ON PKview.PK_Table = isc.TABLE_NAME 
	
WHERE (1=1) 
AND isc.TABLE_NAME LIKE 'T[_]%' ESCAPE '\' 

AND isc.TABLE_NAME NOT IN 
(
	 'sysdiagrams', 'dtproperties' 
	,'ELMAH_Error', 'T_COR_Error', 'T_DMS_Sessions', 'T_FMS_Dashboard_27082014' 
	,'T_Export_Raum', 'T_Export_Raum_History'
	
	,'T_Benutzerrechte' -- UID 
	,'T_Benutzergruppen' -- ID 
	,'T_LOG_ProzedurRun' --	ID
	,'T_AP_Ref_Mandant' -- MDT_ID

	,'T_AP_Gebaeude_SwissLife'
	,'T_AP_Gebaeude_Reichle'
	,'T_AP_Geschoss_Reichle'
	,'T_AP_Geschoss_Reichle'			
	,'T_AP_Ref_Kostenstelle_Raiffeisen'
	,'T_AP_Ref_Organisationseinheit_Raiffeisen'
	,'T_AP_Ref_Nutzungsart_Raiffeisen'
	,'T_AP_Ref_MobiliarBibliothek_Raiffeisen'
	
	,'T_SYS_Language_DayNames', 'T_SYS_Language_MonthNames', 'T_SYS_Language_Forms',  'T_SYS_Language_Forms_i18n', 'T_SYS_Language_Forms_i18n_Cust' 
	,'T_AP_Legenden', 'T_AP_StichtagVisualisierung', 'T_SYS_VisualisierungStichtag' 
	,'T_AP_LinkDWG', 'T_AP_DWGSAVE', 'T_AP_SHOW_BeilageMV', 'T_ZO_AP_SHOW_Geschoss_Reinigungsplanung' 
	,'T_ZO_Benutzer_Mandant', 'T_ZO_AP_Schluesselgruppe_Mandant', 'T_ZO_AP_Zylinder_Mandant', 'T_ZO_REM_AP_Planung_Ref_Raum' 
	,'ZZ_TIMESTAMPS', '__Import_AP_Objekte' 
) -- End not in 

-- ORDER BY PK_Table 

ORDER BY TABLE_NAME
; 

SELECT 
	 PotFK.POTFK_TABLE_NAME 
	,PotFK.POTFK_COLUMN_NAME 
	,PotFK.POTFK_PK_Table 
	,PotFK.POTFK_PK_Column 
	,V_DELDATA_ForeignKeyRelations.FK_CONSTRAINT_NAME
FROM _______aaaaaaa AS PotFK 

LEFT JOIN V_DELDATA_ForeignKeyRelations 
	ON V_DELDATA_ForeignKeyRelations.FK_TABLE_NAME = PotFK.POTFK_TABLE_NAME 
	AND V_DELDATA_ForeignKeyRelations.FK_COLUMN_NAME = PotFK.POTFK_COLUMN_NAME 
	AND V_DELDATA_ForeignKeyRelations.REFERENCED_TABLE_NAME = PotFK.POTFK_PK_Table 
	AND V_DELDATA_ForeignKeyRelations.REFERENCED_COLUMN_NAME= PotFK.POTFK_PK_Column 
	
-- WHERE FK_CONSTRAINT_NAME IS NOT NULL 
WHERE FK_CONSTRAINT_NAME IS NULL 

ORDER BY POTFK_TABLE_NAME, POTFK_COLUMN_NAME


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo._______aaaaaaa') AND type in (N'U'))
DROP TABLE dbo._______aaaaaaa 
GO