How to Identify Foreign Keys Without Index

The below query helps you to identify the foreign key column in a Database\Tables without any index.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName

,t.name AS TableName

,fk.name AS ConstraintName

,c.name AS ColumnName

FROM sys.tables t

JOIN sys.columns c ON c.object_id = t.object_id

JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id

JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id

AND fkc.parent_column_id = c.column_id

LEFT JOIN sys.index_columns ic ON ic.object_id = fkc.parent_object_id

AND ic.column_id = fkc.parent_column_id

WHERE ic.object_id IS NULL

ORDER BY t.name