Thanks Jack, your script will return 25 rows from AdventureWorks database.

Script I wrote returns 68 rows.

Why, I'm looking on all columns included in index and want to have same construction as FK,

even if FK include more then one column.

This script return the same result as yours, but it isn't what I want.

SELECTOBJECT_NAME(parent_object_id) AS table_name,

OBJECT_NAME(constraint_object_id) AS fk_name

FROMsys.foreign_key_columns AS fkc

LEFT JOINsys.index_columns AS ic

ONfkc.parent_object_id = ic.object_id

ANDfkc.parent_column_id = ic.column_id

WHEREic.object_id IS NULL

ORDER BYtable_name, fk_name

Just done one change in my first script, now it will look even if you have an index there first column are same as FK first column.

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

))

)

Regards

/Semko