SQL-Foreign-Key-WIth-No-Indexes.sql

-- See blog post: https://sqlpal.blogspot.com/2018/05/dmv-to-list-of-foreign-keys-with-no.html

;
WITH    fk_cte
          AS ( SELECT   OBJECT_NAME(fk.referenced_object_id) pk_table ,
                        c2.name pk_column ,
                        kc.name pk_index_name ,
                        OBJECT_NAME(fk.parent_object_id) fk_table ,
                        c.name fk_column ,
                        fk.name fk_name ,
                        CASE WHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END does_fk_has_index ,
                        i.is_primary_key is_fk_a_pk_also ,
                        i.is_unique is_index_on_fk_unique ,
                        fk.*
               FROM     sys.foreign_keys fk
                        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
                        INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
                        LEFT  JOIN sys.columns c2 ON c2.object_id = fk.referenced_object_id AND c2.column_id = fkc.referenced_column_id
                        LEFT JOIN sys.key_constraints kc ON kc.parent_object_id = fk.referenced_object_id AND kc.type = 'PK'
                        LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        LEFT JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
             )
    SELECT  * FROM    fk_cte
       LEFT JOIN sys.dm_db_partition_stats ps on ps.object_id = fk_cte.parent_object_id and ps.index_id <= 1
    WHERE   does_fk_has_index = 0 -- and fk_table = 'LineItems'
    ORDER BY used_page_count desc