Script to add an index on each foreign key?

Question

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?

Answer 1

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.