Our standard is to always create foreign key indexes.
These indexes exactly match the columns of the DRI-definition.
This is to avoid scans when parent rows are being deleted.
Stored procedure [Spc_ALZDBA_Create_FK_Ix] will only script the "CREATE INDEX" statements.
You'll have to copy/paste and execute yourself, based on your own preferences.
This select statement will generate exec-statements for all base tables.
Copy/paste and execute or declare a cursor and execute immediate.
-- Execution example
select 'exec Spc_ALZDBA_Create_FK_Ix @ParentTbName = '''+ T.TABLE_NAME + ''' ,@ParentTbOwner = ''' + T.TABLE_SCHEMA + ''', @CheckExistIx = 1 ' + char(10) + 'go'
from INFORMATION_SCHEMA.Tables T
where T.TABLE_TYPE = 'BASE TABLE'
order by T.TABLE_SCHEMA, T.TABLE_NAME
-- Execution example for the procedure
exec Spc_ALZDBA_Create_FK_Ix @ParentTbName = 'Address' ,@ParentTbOwner = 'Person', @CheckExistIx = 1
-- result:
Create index [XFK_ALZ_FK_EmployeeAddress_Address_AddressID] on [HumanResources].[EmployeeAddress] ( AddressID ) ;
GO
This procedure will only script the "CREATE INDEX" statements.
You'll have to copy/paste and execute yourself, based on your own preferences.
TEST IT - TEST IT - TEST IT - TEST IT
