Generate Create FK-indexes
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
-- drop procedure Spc_ALZDBA_Create_FK_Ix
-- go
Create procedure Spc_ALZDBA_Create_FK_Ix
@ParentTbName varchar(128)
, @ParentTbOwner varchar(128) = NULL
, @CheckExistIx bit = 1
-- with encryption
as
begin
-- 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
-- as
-- creatie test case begin
-- if object_id('T_ParentMC') is null
-- begin
-- create table T_ParentMC (
-- col1 int identity(1,1) not null ,
-- col2 int not null,
-- col3 int not null,
-- col4 int not null,
-- col5 varchar(100) not null default ''
-- , CONSTRAINT PK_ParentMC PRIMARY KEY (col2,col3,col4)
-- )
-- print 'table T_ParentMC created'
-- end
-- go
--
-- if object_id('T_ChildMC') is null
-- begin
-- create table T_ChildMC (
-- col1 int identity(1,1) not null primary key,
-- col2FK1_1 int not null,
-- col3FK1_2 int not null,
-- col4FK1_3 int not null,
-- col5 varchar(100) not null default ''
-- , CONSTRAINT FK_C2P FOREIGN KEY (col2FK1_1, col3FK1_2, col4FK1_3)
-- REFERENCES T_ParentMC (col2,col3,col4)
-- )
-- print 'table T_ChildMC created'
-- end
-- go
-- -- creatie test case einde
--
--
-- Declare @ParentTbName varchar(128)
-- , @ParentTbOwner varchar(128)
-- select @ParentTbName = 'T_ParentMC', @ParentTbOwner = NULL
SET nocount on
create table #tmpPKeys(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname not null,
TABLE_NAME sysname not null,
COLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
PK_NAME sysname null )
Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)
-- Get PK-info
insert into #tmpPKeys
exec sp_pkeys @table_name = @ParentTbName
, @table_owner = @ParentTbOwner
-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName
create table #tmpFKeys
(PKTABLE_QUALIFIER sysname not null,
PKTABLE_OWNER sysname not null,
PKTABLE_NAME sysname not null,
PKCOLUMN_NAME sysname not null,
FKTABLE_QUALIFIER sysname not null,
FKTABLE_OWNER sysname not null,
FKTABLE_NAME sysname not null,
FKCOLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
UPDATE_RULE smallint not null,
DELETE_RULE smallint not null,
FK_NAME sysname not null,
PK_NAME sysname not null,
DEFERRABILITY int not null)
Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)
-- Get FK-info (all dependant objects)
insert into #tmpFKeys
exec sp_fkeys @pktable_name = @ParentTbName
, @pktable_owner = @ParentTbOwner
-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]
-- { , [ @fktable_name = ] 'fktable_name' }
-- [ , [ @fktable_owner = ] 'fktable_owner' ]
-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]
-- print 'Begin transaction trxCreateIx_' + @ParentTbName
-- print ' '
-- Detect indexes for dependant tables
create table #tmpExistIx ( FKTABLE_OWNER varchar(128) not null default '-------------------------------------------',
FKTABLE_NAME varchar(128) not null default '-------------------------------------------',
index_name varchar(128) not null,
index_description varchar(512) not null,
index_keys varchar(512) not null )
declare @FQTable varchar(257)
, @FKTABLE_OWNER varchar(128)
, @FKTABLE_NAME varchar(128)
declare csrHelpIx cursor
for select FKTABLE_OWNER, FKTABLE_NAME, FKTABLE_OWNER + '.' + FKTABLE_NAME as FQTable from #tmpFKeys
open csrHelpIx
FETCH NEXT FROM csrHelpIx INTO @FKTABLE_OWNER, @FKTABLE_NAME, @FQTable
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #tmpExistIx (index_name ,index_description, index_keys )
exec sp_helpindex @FQTable
update #tmpExistIx
set FKTABLE_OWNER = @FKTABLE_OWNER
, FKTABLE_NAME = @FKTABLE_NAME
where FKTABLE_OWNER = '-------------------------------------------'
FETCH NEXT FROM csrHelpIx INTO @FKTABLE_OWNER, @FKTABLE_NAME, @FQTable
END
-- Cursor afsluiten
CLOSE csrHelpIx
DEALLOCATE csrHelpIx
-- print '-- Create FK-Indexes'
-- print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'
select 'Create index [XFK_ALZ_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+
FK1.FKCOLUMN_NAME
+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end
+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end
+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end
+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end
+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end
+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end
+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end
+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end
+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end
+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end
+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end
+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end
+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end
+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end
+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end
+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end
+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end
+ ' ) ;' + char(10) + 'GO '
from #tmpFKeys FK1
left join #tmpFKeys FK2
on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2
left join #tmpFKeys FK3
on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3
left join #tmpFKeys FK4
on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4
left join #tmpFKeys FK5
on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5
left join #tmpFKeys FK6
on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6
left join #tmpFKeys FK7
on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7
left join #tmpFKeys FK8
on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8
left join #tmpFKeys FK9
on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9
left join #tmpFKeys FK10
on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10
left join #tmpFKeys FK11
on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11
left join #tmpFKeys FK12
on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12
left join #tmpFKeys FK13
on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13
left join #tmpFKeys FK14
on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14
left join #tmpFKeys FK15
on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15
left join #tmpFKeys FK16
on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16
left join #tmpFKeys FK17
on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17
left join #tmpFKeys FK18
on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18
where FK1.KEY_SEQ = 1
AND ( @CheckExistIx <> 1
OR NOT EXISTS (select * from #tmpExistIx FKIx
where FKIx.FKTABLE_OWNER = FK1.FKTABLE_OWNER
and FKIx.FKTABLE_NAME = FK1.FKTABLE_NAME
and FKIx.index_keys like FK1.FKCOLUMN_NAME + '%' )
)
order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME
-- print 'Commit transaction trxCreateIx_' + @ParentTbName
-- cleanup
drop table #tmpPKeys
drop table #tmpFKeys
drop table #tmpExistIx
-- cleanup when testing is done
-- drop table T_ChildMC
-- drop table T_ParentMC
--
end 
