Script to find Foreign Key missing Index?

  • Hi,

    just now I'm working with performance tuning on a SQL Server 2005.

    I need an script that can find all Foreign Key missing Index.

    I mean I want to find all tables which have Foreign Key

    and are missing an index on same columns which are included in Foreign Key.

    Regards

    /Semko

  • Here is a link to a script I submitted that will find all foreign keys:

    http://www.sqlservercentral.com/scripts/foreign+keys/64333/

    It wouldn't be too hard to extend it using sys.index_columns and sys.indexes

  • Thanks for your replay Jack, the result I want is to return a list of all tables, which have FK and are missing an index on same columns as FK.

    Code to solution I want to have is:

    --CODE START

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

    IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable

    --Create index temp table

    CREATE TABLE #t1

    ( do integer default(0),

    index_name varchar(100),

    index_descrip varchar(200),

    index_keys varchar(200),

    table_name varchar(100))

    --Create FK temp table

    CREATE TABLE #FKTable

    ( fk_name varchar(100),

    fk_keys varchar(200),

    fk_keyno int,

    table_name varchar(100))

    --Collect and uppdate all index info

    EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"

    UPDATE #t1 SET table_name = replace(table_name , '[', '')

    UPDATE #t1 set table_name = replace(table_name , ']', '')

    --Collect all index info

    INSERT INTO #FKTable

    SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno,

    s.name + '.' + OBJECT_NAME(fkeyid) AS TabName

    FROM sysforeignkeys k

    JOIN sys.objects c

    ON k.constid = c.object_id

    JOIN sys.schemas s

    ON c.schema_id = s.schema_id

    --If FK have two or more columns add them in one row to be able to compare with index columns.

    DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)

    DECLARE FKCurusor CURSOR FOR

    SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn

    FROM sysforeignkeys k

    JOIN sysobjects c

    ON k.constid = c.id

    WHERE keyno > 1

    ORDER BY keyno

    DELETE FROM #FKTable WHERE fk_keyno > 1

    OPEN FKCurusor

    FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    UPDATE #FKTable SET

    fk_keys = fk_keys + ', ' + @FKColumn

    WHERE fk_name = @FKName

    FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn

    END

    CLOSE FKCurusor

    DEALLOCATE FKCurusor

    /*

    SELECT * FROM #FKTable

    ORDER BY table_name

    SELECT * FROM #t1

    ORDER BY table_name

    */

    PRINT '

    ---------------------------------------------------------------------

    FK MISSING Indexes

    ----------------------------------------------------------------------------

    '

    SELECT DISTINCT table_name, fk_name

    FROM #FKTable f1

    WHERE NOT EXISTS (

    SELECT fk_name

    FROM #FKTable f

    INNER JOIN #t1 t

    ON f.table_name = t.table_name

    WHERE f1.fk_name = f.fk_name

    AND fk_keys = index_keys

    )

    --CODE END

    Regards

    /Semko

  • I understood what you were looking for, which is why I said that the script I linked to would be a starting point. If you use the script I linked to and the other system views I mention you can get the information you are looking for in one query. Something like:

    [font="Courier New"]SELECT

       RC.Constraint_Name AS FK_Constraint,

       RC.Constraint_Catalog AS FK_Database,

       RC.Constraint_Schema AS FK_Schema,

       CCU.Table_Name AS FK_Table,

       CCU.Column_Name AS FK_Column

    FROM

       information_schema.referential_constraints RC JOIN

       INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON

           RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN

       INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON

           RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME LEFT JOIN

       sys.columns C ON

           CCU.Column_Name = C.name AND

           CCU.Table_Name = OBJECT_NAME(C.OBJECT_ID) LEFT JOIN

       sys.index_columns IC ON

           C.OBJECT_ID = IC.OBJECT_ID AND

           C.column_id = IC.column_id LEFT JOIN

       sys.indexes I ON

           IC.OBJECT_ID = I.OBJECT_ID AND

           IC.index_Id = I.index_Id

    WHERE

       I.name IS NULL

    ORDER BY

       RC.Constraint_NAME  

       [/font]

    This is not perfected, but it does do what I think you want it to do. It will show any tables with Foreign Keys where any column of that foreign key does not have an index on it.

  • Thanks Jack, your script will return 25 rows from AdventureWorks database.

    Script I wrote returns 68 rows.

    Why, I'm looking on all columns included in index and want to have same construction as FK,

    even if FK include more then one column.

    This script return the same result as yours, but it isn't what I want.

    SELECTOBJECT_NAME(parent_object_id) AS table_name,

    OBJECT_NAME(constraint_object_id) AS fk_name

    FROMsys.foreign_key_columns AS fkc

    LEFT JOINsys.index_columns AS ic

    ONfkc.parent_object_id = ic.object_id

    ANDfkc.parent_column_id = ic.column_id

    WHEREic.object_id IS NULL

    ORDER BYtable_name, fk_name

    Just done one change in my first script, now it will look even if you have an index there first column are same as FK first column.

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

    IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable

    --Create index temp table

    CREATE TABLE #t1

    (

    do integer default(0),

    index_name varchar(100),

    index_descrip varchar(200),

    index_keys varchar(200),

    table_name varchar(100))

    --Create FK temp table

    CREATE TABLE #FKTable

    (

    fk_name varchar(100),

    fk_keys varchar(200),

    fk_keyno int,

    table_name varchar(100))

    --Collect and uppdate all index info

    EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"

    UPDATE #t1 SET table_name = replace(table_name , '[', '')

    UPDATE #t1 set table_name = replace(table_name , ']', '')

    --Collect all index info

    INSERT INTO #FKTable

    SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno,

    s.name + '.' + OBJECT_NAME(fkeyid) AS TabName

    FROM sysforeignkeys k

    JOIN sys.objects c

    ON k.constid = c.object_id

    JOIN sys.schemas s

    ON c.schema_id = s.schema_id

    --If FK have two or more columns add them in one row to be able to compare with index columns.

    DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)

    DECLARE FKCurusor CURSOR FOR

    SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn

    FROM sysforeignkeys k

    JOIN sysobjects c

    ON k.constid = c.id

    WHERE keyno > 1

    ORDER BY keyno

    DELETE FROM #FKTable WHERE fk_keyno > 1

    OPEN FKCurusor

    FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    UPDATE #FKTable SET

    fk_keys = fk_keys + ', ' + @FKColumn

    WHERE fk_name = @FKName

    FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn

    END

    CLOSE FKCurusor

    DEALLOCATE FKCurusor

    /*

    SELECT * FROM #FKTable

    ORDER BY table_name

    SELECT * FROM #t1

    ORDER BY table_name

    */

    PRINT '

    ---------------------------------------------------------------------

    FK MISSING Indexes

    ----------------------------------------------------------------------------

    '

    SELECT DISTINCT table_name, fk_name

    FROM #FKTable f1

    WHERE NOT EXISTS (

    SELECT fk_name

    FROM #FKTable f

    INNER JOIN #t1 t

    ON f.table_name = t.table_name

    WHERE (f1.fk_name = f.fk_name

    AND fk_keys = index_keys)

    OR ( f1.fk_name = f.fk_name

    AND fk_keys = SUBSTRING (index_keys, 1 ,

    CASE

    WHEN CHARINDEX( ',',index_keys)= 0 THEN 0

    ELSE CHARINDEX( ',',index_keys) -1

    END

    ))

    )

    Regards

    /Semko

  • Nice script. Is was almost what i was looking for. I added an extra column to the output, containing the DDL to create the missing index:

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

    IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable

    --Create index temp table

    CREATE TABLE #t1

    (

    do integer default(0),

    index_name varchar(100),

    index_descrip varchar(200),

    index_keys varchar(200),

    table_name varchar(100))

    --Create FK temp table

    CREATE TABLE #FKTable

    (

    fk_name varchar(100),

    fk_keys varchar(200),

    fk_keyno int,

    table_name varchar(100))

    --Collect and uppdate all index info

    EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"

    UPDATE #t1 SET table_name = replace(table_name , '[', '')

    UPDATE #t1 set table_name = replace(table_name , ']', '')

    --Collect all index info

    INSERT INTO #FKTable

    SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno,

    s.name + '.' + OBJECT_NAME(fkeyid) AS TabName

    FROM sysforeignkeys k

    JOIN sys.objects c

    ON k.constid = c.object_id

    JOIN sys.schemas s

    ON c.schema_id = s.schema_id

    --If FK have two or more columns add them in one row to be able to compare with index columns.

    DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)

    DECLARE FKCurusor CURSOR FOR

    SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn

    FROM sysforeignkeys k

    JOIN sysobjects c

    ON k.constid = c.id

    WHERE keyno > 1

    ORDER BY keyno

    DELETE FROM #FKTable WHERE fk_keyno > 1

    OPEN FKCurusor

    FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    UPDATE #FKTable SET

    fk_keys = fk_keys + ', ' + @FKColumn

    WHERE fk_name = @FKName

    FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn

    END

    CLOSE FKCurusor

    DEALLOCATE FKCurusor

    /*

    SELECT * FROM #FKTable

    ORDER BY table_name

    SELECT * FROM #t1

    ORDER BY table_name

    */

    PRINT '

    ---------------------------------------------------------------------

    FK MISSING Indexes

    ----------------------------------------------------------------------------

    '

    SELECT DISTINCT table_name,fk_keys, 'CREATE NONCLUSTERED INDEX [' + fk_name + '] ON ' + table_name + '(' +fk_keys + ' ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'

    FROM #FKTable f1

    WHERE NOT EXISTS (

    SELECT fk_name

    FROM #FKTable f

    INNER JOIN #t1 t

    ON f.table_name = t.table_name

    WHERE (f1.fk_name = f.fk_name

    AND fk_keys = index_keys)

    OR ( f1.fk_name = f.fk_name

    AND fk_keys = SUBSTRING (index_keys, 1 ,

    CASE

    WHEN CHARINDEX( ',',index_keys)= 0 THEN 0

    ELSE CHARINDEX( ',',index_keys) -1

    END

    )))

  • Please note: 2 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • And even 2.5 years later:

    Thanks hanskappert!

    Njål

  • ...and most of all thanks Semko!

  • Yeah, i know, the conversation is years old. But SQLCop and Red Gate and everyone else looking to identify missing foreign key indexes should probably be reading this thread.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply