SQL 2005 and foreign key indexes

SQL Server by default does not index foreign keys (to my knowledge).  So, here is a little script that will generate your create index statements for you.  Note this only works in SQL 2005 due to the new sys table schema...

select 'create index IDX_' + c.[name] + '_' + b.[name] + ' on ' + c.[name] + '(' + b.[name] + ')'
from sys.foreign_key_columns a
    inner join sys.columns b
        on a.parent_object_id = b.object_id and a.parent_column_id = b.column_id
    inner join sys.tables c
        on b.object_id = c.object_id