I have a SQL Server 2000 database with approximately 220 tables. These tables have a number foreign key relationships between them. Through performance analysis, we've discovered a number of these foreign key relationships are missing indexes. Instead of being reactive to performance problems, I'd like to be pro-active and find all foreign keys that are missing indexes.
How can I programmatically determine which foreign key are missing indexes?
SELECT * FROM sys.foreign_keys fk WHERE EXISTS ( SELECT * FROM sys.foreign_key_columns fkc WHERE fkc.constraint_object_id = fk.object_id AND NOT EXISTS ( SELECT * FROM sys.index_columns ic WHERE ic.object_id = fkc.parent_object_id AND ic.column_id = fkc.parent_column_id AND ic.index_column_id = fkc.constraint_column_id ) ) I don't have a copy of SQL Server 2000 handy, but you may need to change sys.foreign_key to sysforeignkeys etc., like described here.
This query selects all foreign keys which don't have an index covering all columns that comprise the key.
This supports multi-column foreign keys just as well.
This, however, will return a false positive if there is a composite index that covers all columns but they are not the leftmost columns in this index.
Like, if there is a FOREIGN KEY (col2, col3) and an index on (col1, col2, col3), this will return that there is an index despite the fact this index is unusable for this foreign key.
AND ic.index_column_id = fkc.constraint_column_id in the NOT EXISTS to ensure the index is in the correct index key column order too. Otherwise, exactly what I use day to day
Here is an answer that works for SQL Server 2000 authored by a co-worker:
/* Description: This script outputs a table with all the current database un-indexed foreign keys. The table has three columns ( TableName , ColumnName, ForeignKeyName ) TableName: The table containing the un-indexed foreign key ColumnName: The foreign key column that’s not indexed ForeignKeyName: Name of foreign key witch column doesn’t have an index */ DECLARE @TableName varchar(255), @ColumnName varchar(255), @ForeignKeyName sysname SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE FKColumns_cursor CURSOR Fast_Forward FOR SELECT cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME WHERE ic.CONSTRAINT_TYPE = 'FOREIGN KEY' CREATE TABLE #temp1( TableName varchar(255), ColumnName varchar(255), ForeignKeyName sysname ) OPEN FKColumns_cursor FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName WHILE @@FETCH_STATUS = 0 BEGIN IF ( SELECT COUNT(*) FROM sysobjects o INNER JOIN sysindexes x ON x.id = o.id INNER JOIN syscolumns c ON o.id = c.id INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid WHERE o.type in ('U') AND xk.keyno <= x.keycnt AND permissions(o.id, c.name) <> 0 AND (x.status&32) = 0 AND o.name = @TableName AND c.name = @ColumnName ) = 0 BEGIN INSERT INTO #temp1 SELECT @TableName, @ColumnName, @ForeignKeyName END FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName END CLOSE FKColumns_cursor DEALLOCATE FKColumns_cursor SELECT * FROM #temp1 ORDER BY TableName Built on the above code to drop the temp table and get scripts to create the indexes.
/* Description: */ DECLARE @SchemaName varchar(255), @TableName varchar(255), @ColumnName varchar(255), @ForeignKeyName sysname SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE FKColumns_cursor CURSOR Fast_Forward FOR SELECT cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME WHERE ic.CONSTRAINT_TYPE = 'FOREIGN KEY' CREATE TABLE #temp1( SchemaName varchar(255), TableName varchar(255), ColumnName varchar(255), ForeignKeyName sysname ) OPEN FKColumns_cursor FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName WHILE @@FETCH_STATUS = 0 BEGIN IF ( SELECT COUNT(*) FROM sysobjects o INNER JOIN sysindexes x ON x.id = o.id INNER JOIN syscolumns c ON o.id = c.id INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid WHERE o.type in ('U') AND xk.keyno <= x.keycnt AND permissions(o.id, c.name) <> 0 AND (x.status&32) = 0 AND o.name = @TableName AND c.name = @ColumnName ) = 0 BEGIN INSERT INTO #temp1 SELECT @SchemaName, @TableName, @ColumnName, @ForeignKeyName END FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName END CLOSE FKColumns_cursor DEALLOCATE FKColumns_cursor SELECT 'CREATE INDEX IDX_' + ForeignKeyName + ' ON ' + SchemaName + '.' + TableName + '(' + ColumnName +')' FROM #temp1 ORDER BY TableName drop table #temp1 In my post "SQL Script to create indexes for Foreign keys" I've put links to 2 implementations: paul_nielsen’s and tklimczak's (login to sqlservercentral is required)
Firstly: list the columns with a foreign key constraint. This will help:
Query to get all foreign key constraints in SQL Server 2000
Cross-compare with sysindexes and syscolumns tables; the keys field in sysindexes has a list of all keys in the index.
Note: This is for SQL Server 2005+ but this was the only question I found about this topic.
--Finds foreign keys without indexes --How to interpret: --When we delete frpm PkTable, it checks FkColumn for the PkId we are deleting. --So if FkTable doesn't have an index on FkColumn, then we cannot delete a row from PkTable because it is too slow. SELECT rt.name as PkTableName, rc.name as PkColumnName, fk.name FkName, t.name as FkTableName, c.name as FkColumnName, ddps.row_count, i.name as IndexName FROM sys.foreign_key_columns fkc inner join sys.foreign_keys fk on fkc.constraint_object_id = fk.object_id inner join sys.tables t on fkc.parent_object_id = t.object_id inner join sys.columns c on fkc.parent_object_id = c.object_id and fkc.parent_column_id = c.column_id inner join sys.tables rt on fkc.referenced_object_id = rt.object_id inner join sys.columns rc on fkc.referenced_object_id = rc.object_id and fkc.referenced_column_id = rc.column_id inner join sys.indexes ri on t.object_id = ri.object_id inner JOIN sys.dm_db_partition_stats AS ddps ON ri.OBJECT_ID = ddps.OBJECT_ID AND ri.index_id = ddps.index_id left join sys.index_columns ic on ic.object_id = t.object_id and ic.column_id = c.column_id left join sys.indexes i on ic.object_id = i.object_id and i.index_id = ic.index_id where ri.index_id < 2 and i.index_id is null and ddps.row_count > 0 order by --PkTableName, ddps.row_count desc