Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to find Foreign Key missing Index? Expand / Collapse
Author
Message
Posted Tuesday, September 16, 2008 12:50 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:11 AM
Points: 712, Visits: 189
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
Post #569970
Posted Tuesday, September 16, 2008 6:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #570171
Posted Wednesday, September 17, 2008 7:22 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:11 AM
Points: 712, Visits: 189
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

Post #570981
Posted Wednesday, September 17, 2008 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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:

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  

  


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.








Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #571019
Posted Thursday, September 18, 2008 1:43 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:11 AM
Points: 712, Visits: 189
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.

SELECT		OBJECT_NAME(parent_object_id) AS table_name,
OBJECT_NAME(constraint_object_id) AS fk_name
FROM sys.foreign_key_columns AS fkc
LEFT JOIN sys.index_columns AS ic
ON fkc.parent_object_id = ic.object_id
AND fkc.parent_column_id = ic.column_id
WHERE ic.object_id IS NULL
ORDER BY table_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
Post #571527
Posted Friday, August 27, 2010 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:49 AM
Points: 1, Visits: 6
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
)))


Post #976291
Posted Friday, August 27, 2010 5:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
Please note: 2 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #976304
Posted Wednesday, January 30, 2013 6:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:12 AM
Points: 2, Visits: 22
And even 2.5 years later:
Thanks hanskappert!

Njål
Post #1413600
Posted Wednesday, January 30, 2013 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:12 AM
Points: 2, Visits: 22
...and most of all thanks Semko!
Post #1413603
Posted Tuesday, July 1, 2014 6:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 99, Visits: 764
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.
Post #1587979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse