Find UnIndexed Foreign Keys

The following script will find all the foreignkeys on tables that are not currently apart of an Index for the table. This can be used to identify possible additional indexes that may need to be added to help with performance. Not all FKs need to be in an index, but it may point out some that really should be.

select
a1.name TableName
, a2.name FKName
, c1.name ColumnName
from sysobjects a1
inner join sysforeignkeys b1
on a1.id = b1.fkeyid
inner join sysobjects a2
on a2.id = b1.constid
inner join syscolumns c1
on c1.id = a1.id
and c1.colid = b1.fkey
where not exists (
select *
from sysobjects a
inner join sysindexes b
on b.id = a.id
inner join sysindexkeys k
on k.id= a.id
and k.indid = b.indid
inner join syscolumns c
on c.id = a.id
and c.colid = k.colid
where a.name not like 'sys%'
and b.name not like '_WA%'
and c.colid = b1.fkey
and c.id = a1.id
)
order by
a1.name
, a2.name
, c1.name