Script to add an index on each foreign key?

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.

Ok, here is my take on this. I added support for schemes and also check if an index exists with the current naming convention. This way as you modify your tables you can check for missing indexes.

   SELECT 'CREATE NONCLUSTERED INDEX IX_' + s.NAME + '_' + o.NAME + '__' + c.NAME + ' ON ' + s.NAME + '.' + o.NAME + ' (' + c.NAME + ')'
    FROM sys.foreign_keys fk
    INNER JOIN sys.objects o ON fk.parent_object_id = o.object_id
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id
        AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.tables t ON t.object_id = o.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    LEFT JOIN sys.indexes i ON i.NAME = ('IX_' + s.NAME + '_' + o.NAME + '__' + c.NAME)
    WHERE i.NAME IS NULL
    ORDER BY o.NAME

I modified the query to use they system views. It will also script every FK in the table not just one.

SELECT 'CREATE NONCLUSTERED INDEX ndx_' + o.name + '__' + c.name 
   + ' ON ' + o.name + ' (' + c.name + ')'
FROM   sys.foreign_keys        fk
JOIN   sys.objects             o    ON  fk.parent_object_id = o.object_id
JOIN   sys.foreign_key_columns fkc  ON  fk.object_id = fkc.constraint_object_id
JOIN   sys.columns             c    ON  fkc.parent_object_id = c.object_id
                                AND fkc.parent_column_id = c.column_id
ORDER BY o.name