Script to add an index on each foreign key?
foreign-keys, indexing, sql-server
Solution
OK, I worked this out myself - here it is for everyone else's benefit...
select 'create index IX_'+c.name+'_'+p.name+' on '+c.name+'('+cf.name+');'
from sysforeignkeys fk join sysobjects c on fk.fkeyid=c.id
join sysobjects p on fk.rkeyid=p.id
join syscolumns cf on c.id=cf.id and cf.colid = fk.fkey
left join sysindexkeys k on k.id = cf.id and k.colid = cf.colid
where k.id is null
order by c.name
It doesn't work 100%, such as if you have two FKs on one table to the same primary table, but there are few enough instances of this (in my DB at least) that I could viably correct these by hand.
Problem
Seeing as a foreign key does not automatically create an index in SQL Server, I want to create an explicit index on every FK field in my database. And I have over 100 tables in the schema... So, does anyone have a ready packaged script that I could use to detect all FKs and create an index on each?