Finding Foreign Keys without Indexes

I have seen a number of blog posts on this topic that go into detail as to why it is important to have such indexes and sometimes provide helpful queries. Examples would include:
    http://www.brentozar.com/archive/2011/09/indexing-for-deletes/
    http://michaeljswart.com/2009/12/finding-your-missing-keys/

However, I wanted to take it a step further. The following query not only finds the Foreign Keys without an associated Index, it also provides the command needed to create each index in both XML and text formats.

If you click on the XML result, SSMS will open a new window containing the command already nicely formatted for you to copy into your source control system of preference, or into a new SSMS window so you can execute it. Of course, the query as shown uses my preferred naming and formatting standards for the indexes, but it should be easy enough to modify it to use your own.

/* What indexes are needed for my foreign keys.sql

Purpose:    Find foreign keys without an index and the command needed to create the index.
            Note that if these aren't showing up in the missing_index* views you might not
            think that you need them, but not having them will cause some deletes from
            referenced tables to perform full table scans. For example, without the
            appropriate foreign key index, deleting a customer would result in a full scan
            of the invoice table to ensure that the customer PK does not exist in any
            invoice record.

Author:     Mark Freeman (@m60freeman)

Changes:    2011-09-01  Created
            2011-11-20  Eliminated matches to index where the referenced column is not the
                         first column in the index, added comment and brackets in Potential
                         DDL, and added handling of objects with the same name in multiple
                         schemas
*/
SELECT      rc.Constraint_Schema                            AS Base_Schema
            ,ccu.Table_Name                                 AS Base_Table
            ,ccu.Column_Name                                AS Base_Column
            ,ccu.Constraint_Name                            AS Foreign_Key
            ,ccu2.Constraint_Schema                         AS Referenced_Schema
            ,ccu2.Table_Name                                AS Referenced_Table
            ,ccu2.Column_Name                               AS Referenced_Column
            ,'IX_' + ccu.Table_Name + '_' + ccu.Column_Name AS Potential_Index_Name
            ,CAST('<?query --' + CHAR(13) +
                'CREATE INDEX [IX_' +
                ccu.Table_Name + '_' + ccu.Column_Name +
                ']' + CHAR(13) +
                CHAR(9) + '-- Needed to support ' +
                ccu.Constraint_Name + CHAR(13) +
                CHAR(9) + 'ON [' + rc.Constraint_Schema +
                '].[' + ccu.Table_Name + ']' + CHAR(13) +
                CHAR(9) + '(' + CHAR(13) +
                CHAR(9) + CHAR(9) + '[' + ccu.Column_Name +
                ']' + CHAR(13) + CHAR(9) + ');'
                + CHAR(13) + ' --?>' AS XML)             AS Potential_DDL_XML
            ,'CREATE INDEX [IX_' + ccu.Table_Name + '_' +
                ccu.Column_Name + ']' + ' ON [' +
                rc.Constraint_Schema + '].[' +
                ccu.Table_Name + ']' +  '([' +
                ccu.Column_Name +
                ']); -- Needed to support '
                + ccu.Constraint_Name                       AS Potential_DDL
FROM        information_schema.referential_constraints rc
            JOIN information_schema.constraint_column_usage ccu
                ON rc.CONSTRAINT_NAME           = ccu.CONSTRAINT_NAME
                    AND rc.CONSTRAINT_SCHEMA    = ccu.CONSTRAINT_SCHEMA
            JOIN information_schema.constraint_column_usage ccu2
                ON rc.UNIQUE_CONSTRAINT_NAME    = ccu2.CONSTRAINT_NAME
                    AND rc.CONSTRAINT_SCHEMA    = ccu2.CONSTRAINT_SCHEMA
WHERE       NOT EXISTS (
                SELECT  1
                FROM    sys.indexes i
                        JOIN sys.index_columns ic
                            ON  ic.[object_id]  = i.[object_id]
                                AND ic.index_Id = i.index_Id
                        JOIN sys.columns c
                            ON  c.[object_id]   = ic.[object_id]
                                AND c.column_id = ic.column_id
                        JOIN sys.objects o
                            ON  o.[object_id]   = c.[object_id]
                        JOIN sys.schemas s
                            ON  s.[schema_id]   = o.[schema_id]
                WHERE   c.name                      = ccu.COLUMN_NAME
                  AND   OBJECT_NAME(c.[object_id])  = ccu.TABLE_NAME
                  AND   s.name                      = ccu.TABLE_SCHEMA
                   AND   ic.key_ordinal              = 1
                )
AND         ccu.Table_Name NOT LIKE 'aspnet_%'
ORDER BY    ccu2.Constraint_Schema,
            ccu2.Table_Name,
            rc.Constraint_Schema,
            ccu.Table_Name,
            ccu.Column_Name;