FYI: Create missing indices on foreign key columns

i asked how create indices on foreign key columns when don't exist. didn't found script, wrote one:

declare @fk table      (        constraint_name sysname ,        parent_name sysname ,        referenced_name sysname ,        parent_column_name sysname ,        referenced_column_name sysname      ) ;    insert  into @fk          select  fk.name as constraint_name ,                  t1.name as parent_name ,                  t2.name as referenced_name ,                  c1.name as parent_column_name ,                  c2.name as referenced_column_name          from    sys.foreign_key_columns fkc                  inner join sys.foreign_keys fk on fk.object_id =  fkc.constraint_object_id                  inner join sys.tables t1 on t1.object_id =  fkc.parent_object_id                  inner join sys.tables t2 on t2.object_id =  fkc.referenced_object_id                  inner join sys.columns c1 on c1.object_id =  fkc.parent_object_id                                               and c1.column_id =  fkc.parent_column_id                  inner join sys.columns c2 on c2.object_id =  fkc.referenced_object_id                                               and c2.column_id =  fkc.referenced_column_id ;    declare @ic table      (        table_name sysname ,        index_name sysname ,        column_name sysname      ) ;    insert  into @ic          select  t.name as table_name ,                  i.name as index_name ,                  c.name as column_name          from    sys.index_columns ic                  inner join sys.tables t on t.object_id = ic.object_id                  inner join sys.indexes on i.object_id = ic.object_id                                              and i.index_id = ic.index_id                  inner join sys.columns c on c.object_id = ic.object_id                                              and c.column_id =  ic.column_id ;  with    fk            as ( select   *                 from     @fk fk                 where    not exists ( select *                                       from   @ic ic                                       where  ic.table_name = fk.parent_name                                              and ic.column_name =  fk.parent_column_name )               )      select  o.constraint_name ,              'create index [ix_' + o.constraint_name + '] on ['              + min(o.parent_name) + '] ('              + ( select  stuff(( select  ', [' + i.parent_column_name + ']'                                  from    fk                                  where   i.constraint_name =  o.constraint_name                                for                                  xml path('')                                ), 1, 2, '')                ) + ') ;' as create_statement      from    fk o      group by o.constraint_name ;

tested against adventureworkslt2008r2 and

create table parent      (        id int identity               not null               primary key ,        payload nvarchar(255) not null      ) ;    create table child      (        id int identity               not null               primary key ,        idparent int not null                     foreign key references parent ( id ) ,        payload nvarchar(255) not null      ) ;    create table parent2      (        id int identity               not null ,        id2 int not null ,        payload nvarchar(255) not null ,        constraint pk_parent2 primary key ( id, id2 )      ) ;    create table child2      (        id int identity               not null               primary key ,        idparent int not null ,        idparent2 int not null ,        payload nvarchar(255) not null ,        constraint fk_child2_parent2 foreign key ( idparent, idparent2 )  references parent2 ( id, id2 )      ) ;

maybe not perfect, comments welcome.

---

microsoft mvp office access
https://mvp.support.microsoft.com/profile/stefan.hoffmann

hi stefan,

i saw blog post greg low 

indexing foreign keys - should sql server automatically? (and check paul nielsen comment blog).

in preparations answer on quiz by paul nielsen.

 

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Transact-SQL