September 16, 2008 at 12:50 am
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
September 16, 2008 at 6:51 am
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
Consultant - Straight Path Solutions
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
September 17, 2008 at 7:22 am
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
September 17, 2008 at 7:58 am
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.
Jack Corbett
Consultant - Straight Path Solutions
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
September 18, 2008 at 1:43 am
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
August 27, 2010 at 5:25 am
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
)))
August 27, 2010 at 5:45 am
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
January 30, 2013 at 6:15 am
And even 2.5 years later:
Thanks hanskappert!
Njål
January 30, 2013 at 6:19 am
...and most of all thanks Semko!
July 1, 2014 at 6:25 am
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