SELECT 
   RC.Constraint_Name AS FK_Constraint,
   RC.Constraint_Catalog AS FK_Database,
   RC.Constraint_Schema AS FK_Schema,
   CCU.Table_Name AS FK_Table,
   CCU.Column_Name AS FK_Column
FROM 
   information_schema.referential_constraints RC JOIN
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON 
       RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
       RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME LEFT JOIN
   sys.columns C ON
       CCU.Column_Name = C.name AND
       CCU.Table_Name = OBJECT_NAME(C.OBJECT_ID) LEFT JOIN
   sys.index_columns IC ON
       C.OBJECT_ID = IC.OBJECT_ID AND
       C.column_id = IC.column_id LEFT JOIN
   sys.indexes I ON
       IC.OBJECT_ID = I.OBJECT_ID AND
       IC.index_Id = I.index_Id 
WHERE
   I.name IS NULL
ORDER BY
   RC.Constraint_NAME  

   

SELECT		OBJECT_NAME(parent_object_id) AS table_name,
			OBJECT_NAME(constraint_object_id) AS fk_name
FROM		sys.foreign_key_columns AS fkc
LEFT JOIN	sys.index_columns AS ic
			ON	fkc.parent_object_id = ic.object_id
			AND	fkc.parent_column_id = ic.column_id
WHERE		ic.object_id IS NULL
ORDER BY	table_name, fk_name

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE  #t1

IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE  #FKTable

--Create index temp table
CREATE TABLE #t1
    (
        do integer default(0), 
        index_name varchar(100), 
        index_descrip varchar(200), 
        index_keys varchar(200), 
        table_name varchar(100)) 
 
 --Create FK temp table   
 CREATE TABLE #FKTable
    (
        fk_name varchar(100), 
        fk_keys varchar(200), 
        fk_keyno int,
        table_name varchar(100)) 
 
--Collect and uppdate all index info   
EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"
UPDATE #t1 SET table_name = replace(table_name , '[', '')
UPDATE #t1 set table_name = replace(table_name , ']', '')
 

--Collect all index info   
INSERT INTO #FKTable
SELECT   OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno, 
s.name + '.' + OBJECT_NAME(fkeyid) AS TabName
FROM sysforeignkeys k 
JOIN sys.objects c 
ON k.constid = c.object_id
JOIN sys.schemas s
ON c.schema_id = s.schema_id
 

--If FK have two or more columns add them in one row to be able to compare with index columns. 
  
DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)

DECLARE FKCurusor CURSOR FOR
SELECT   OBJECT_NAME(constid) AS FKName,  COL_NAME(fkeyid, fkey) AS FKColumn
FROM sysforeignkeys k 
JOIN sysobjects c 
ON k.constid = c.id
WHERE keyno > 1
ORDER BY keyno  
 
 DELETE FROM #FKTable WHERE fk_keyno > 1 
 
 OPEN FKCurusor
	FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	
		UPDATE #FKTable SET 
		fk_keys  = fk_keys  + ', ' + @FKColumn 
		WHERE fk_name = @FKName
	
	
	FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn
	
	END 

CLOSE FKCurusor
DEALLOCATE FKCurusor
/*
SELECT * FROM #FKTable
ORDER BY table_name

SELECT * FROM #t1
ORDER BY table_name
*/
PRINT '
---------------------------------------------------------------------
FK MISSING Indexes
----------------------------------------------------------------------------
'

SELECT DISTINCT table_name, fk_name 
FROM #FKTable f1
WHERE NOT EXISTS (
SELECT  fk_name 
FROM #FKTable f
INNER JOIN #t1 t
ON f.table_name =  t.table_name
WHERE (f1.fk_name = f.fk_name
AND fk_keys  = index_keys)
OR ( f1.fk_name = f.fk_name
AND fk_keys = SUBSTRING (index_keys, 1 , 
CASE 
	WHEN  CHARINDEX( ',',index_keys)= 0 THEN 0 
	ELSE CHARINDEX( ',',index_keys) -1
END
))
)

We walk in the dark places no others will enter
We stand on the bridge and no one may pass