set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DBA_GetForeignKeyColumnsNotInAnyIndex] ()
RETURNS TABLE
AS
/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This is a helpful script to run on a new database or even periodically. Since JOIN
        candidates can usually benefit from an index, this query identifies any ChildTable.ForeignKeyColumns
        that are not in any index whatsoever. It does not matter to this script whether or not the column
        is the leading, middle, or last column in the index. You could easily modify this to find foreign key columns
        that were not the leading column in an index, find those not in a clustered index, et al.
        Feel free to modify this at will!

    Misc Notes: I like this in the "model" database so that it will be propagated to any new databases on the server.
        We can't just dump it in "master" since INFORMATION_SCHEMA views do not change context.
        I wrote this as a function instead of a view because I may wish to add parameters to it later on.
        I prefer this to be a function instead of a proc so that I can write scripts like this:

            SELECT 'CREATE INDEX [nci_' + REPLACE(REPLACE(REPLACE(ChildTable, '[', ''), ']', ''), 'dbo.', '') + '_' + Column_Name + '] ON ' + ChildTable + ' ([' + Column_Name + '])'
            , 'DROP INDEX ' + ChildTable + '.[nci_' + REPLACE(REPLACE(ChildTable, '[', ''), ']', '') + '_' + Column_Name + ']'
            FROM DBA_GetForeignKeyColumnsNotInAnyIndex()
    
    Versions: SQL Server 2005+
    
    Executing this function:
        SELECT * FROM dbo.DBA_GetForeignKeyColumnsNotInAnyIndex ()
        ORDER BY ChildTable, COLUMN_NAME
    
    Creation Date: August 24, 2006

    For more scripts like this one, visit http://Forums.LearnSqlServer.com/CodeSamples.aspx
*/
RETURN (
    -- Foreign Key Columns
    SELECT OBJECT_ID ( tc.TABLE_SCHEMA + '.' + tc.TABLE_NAME ) AS ObjectId
        , '[' + tc.TABLE_SCHEMA + '].[' + tc.TABLE_NAME + ']' AS ChildTable, cu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc -- Returns child table info
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
        ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

    EXCEPT

    -- Columns in Indexes
    SELECT DISTINCT c.object_id
        , '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']' AS TableIdentifier
        , c.name AS ForeignKeyColumnsNotInAnyIndex
    FROM sys.all_columns c JOIN sys.index_columns sc
        ON sc.object_id = c.object_id
        AND sc.column_id = c.column_id
    JOIN INFORMATION_SCHEMA.TABLES t
        ON sc.object_id = OBJECT_ID ( t.TABLE_SCHEMA + '.' + t.TABLE_NAME )
)
GO
SELECT * FROM [DBA_GetForeignKeyColumnsNotInAnyIndex]()