Getting a list of all the un-indexed FK's
-
Is there a to get a list of all the un-indexed foreign Keys in a database?
Thanks in adavance
-
This might get you close to what you want.
select o.name,c.name from sys.sysreferences rinner join sys.syscolumns c
on r.fkeyid = c.id
and r.fkey1 = c.colid
Inner Join sys.sysobjects o
on c.id = o.id
Left Outer Join sys.sysindexkeys k
on c.id = k.id
And c.colid = k.colid
Where k.id is null
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Keep in mind, that is only checking for the first column in a foreign key. You will need to add additional code if there is more than one column on any of your foreign keys.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Thanks for the script!
-
This script looks for indexes whose leading columns exactly match each foreign key, and generates CREATE commands where they are missing. It ignores INCLUDEd index columns.
/*Foreign Key IndexesThis query finds all unindexed foreign keys and generates CREATE INDEX commands.
*/
-- Generate CREATE INDEX scripts for all missing indexes
SELECT'CREATE INDEX [IX_' + object_name(mi.constraint_object_id) + '] ON '
+ QUOTENAME(s.[name]) + '.' + QUOTENAME(t.[name]) + ' ('
+ SUBSTRING(
(SELECT ',' + QUOTENAME(tc.[name])
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS tc ON fkc.parent_object_id = tc.[object_id] AND fkc.parent_column_id = tc.column_id
WHERE fkc.constraint_object_id = mi.constraint_object_id
ORDER BY fkc.constraint_column_id
FOR XML PATH('')),
2, 9999)
+ ') WITH (SORT_IN_TEMPDB=ON, ONLINE=ON)'
FROM (
-- Look for foreign keys where no index exists that matches all columns
SELECT constraint_object_id, parent_object_id
FROM (
-- Compare foreign keys to indexes
-- Count the number of foreign key columns and the number of matching index columns
-- INCLUDEd index columns are not counted
SELECTfk.constraint_object_id, fk.parent_object_id, ix.index_id,
COUNT(fk.parent_column_id) AS FKcolumns,
COUNT(ix.column_id) AS IXcolumns
FROM sys.foreign_key_columns AS fk
LEFT JOIN sys.index_columns AS ix ON fk.parent_object_id = ix.[object_id]
AND fk.parent_column_id = ix.column_id
AND fk.constraint_column_id = ix.index_column_id
AND ix.is_included_column = 0
GROUP BY fk.constraint_object_id, fk.parent_object_id, ix.index_id
) fk_ix
GROUP BY constraint_object_id, parent_object_id
HAVING MAX(FKcolumns) > MAX(IXcolumns)
) mi
INNER JOIN sys.tables t ON mi.parent_object_id = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply