Report on Indexes on FKs

This question had been asked in NG yesterday.I think it is a good exercise so I take a script too get the column usage from both sides (parent/child) written by Aaron Bertrand and modified a little bit to return the table/column that does not have an index on.

WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECTc.[object_id],c.name,c.column_id,ic.index_id
FROM sys.columns c INNER JOIN sys.index_columns ic
ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN sys.foreign_key_columns fkc
ON f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN sys.columns cc
ON fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(
SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;