Here is a handy script to create missing indexes on all foreign keys.
Based on the script over at lukelowrey.com.
-- Call procedure to retrieve error information.
CREATE or ALTER proc [Utility].[usp_GetErrorMessage](
@message nvarchar(max) output,
@procid int = null
)
as
set @message =
case
when @procid is not null then N'Proc=' + object_schema_name(@procid) + N'.' + object_name(@procid) + N', '
else N'<unknown procedure>'
end +
N'ErrorNumber=' + convert(nvarchar(max),error_number()) +
N', Severity=' + convert(nvarchar(max),error_severity()) +
N', State=' + convert(nvarchar(max),error_state()) +
N', Line=' + convert(nvarchar(max),error_line()) +
N', Message=' + convert(nvarchar(max),error_message());
GO
CREATE or ALTER proc [Utility].[usp_AutomaticallyCreateIndexesForForeignKeys](
@Execute bit = null
)
as
begin;
set nocount on;
declare @CurrentSQLCommand nvarchar(max);
declare @IndexName TABLE ( [SQL] nvarchar(450) primary key );
declare @message nvarchar(max);
WITH v_NonIndexedFKColumns AS (
SELECT
[Table_Name] = OBJECT_NAME(a.[parent_object_id]),
[Column_Name] = b.[name]
FROM sys.foreign_key_columns [a]
,sys.all_columns [b]
,sys.objects [c]
WHERE a.[parent_column_id] = b.[column_id]
AND a.[parent_object_id] = b.[object_id]
AND b.[object_id] = c.[object_id]
AND c.[is_ms_shipped] = 0
EXCEPT
SELECT
[Table_Name] = OBJECT_NAME(a.[object_id]),
[Column_Name] = b.[name]
FROM sys.index_columns [a]
,sys.all_columns [b]
,sys.objects [c]
WHERE a.[object_id] = b.[object_id]
AND a.[key_ordinal] = 1
AND a.[column_id] = b.[column_id]
AND a.[object_id] = c.[object_id]
AND c.[is_ms_shipped] = 0
)
INSERT INTO @IndexName( [SQL] )
SELECT
'CREATE INDEX [ix_' + v.[Table_Name] + '_' + v.[Column_Name] + ']'
+ ' ON [' + SCHEMA_NAME(fk.[schema_id]) + '].[' + v.[Table_Name] + '] ([' + v.[Column_Name] + ']);'
FROM v_NonIndexedFKColumns [v]
,sys.all_columns [c]
,sys.all_columns [c2]
,sys.foreign_key_columns [fkc]
,sys.foreign_keys [fk]
WHERE v.[Table_Name] = OBJECT_NAME(fkc.[parent_object_id])
AND v.[Column_Name] = c.[name]
AND fkc.[parent_column_id] = c.[column_id]
AND fkc.[parent_object_id] = c.[object_id]
AND fkc.[referenced_column_id] = c2.[column_id]
AND fkc.[referenced_object_id] = c2.[object_id]
AND fk.[object_id] = fkc.[constraint_object_id];
DECLARE sqlCommands CURSOR FOR SELECT [SQL]
FROM @IndexName;
OPEN sqlCommands;
FETCH NEXT FROM sqlCommands INTO @CurrentSQLCommand;
while @@FETCH_STATUS = 0 begin;
print @CurrentSQLCommand;
if @Execute = 1 begin try;
exec sp_executesql @CurrentSQLCommand;
end try
begin catch;
exec [Utility].[usp_GetErrorMessage] @[email protected] output, @[email protected]@PROCID;
throw 51000, @message, 1;
end catch;
fetch next from sqlCommands into @CurrentSQLCommand;
end;
close sqlCommands;
deallocate sqlCommands;
end;
Protiguous 