ID Columns With No Indexes

This is a really simple script that will tell you what columns with "ID" in the name don't have indexes. Often, foreign key constraints are not added to the database. I'm sure the referrential integrity is being enforced in the business logic of the application. At least I hope so. In any case, the optimizer still needs those columns to be indexed.

select * from sys.columns c 
    where c.name like '%id%'
        and c.object_id not in 
            (
            select object_id from sys.index_columns
            )

Then I created this script to just go ahead and create a basic nonclustered index on those columns. Just copy & paste the results into a SQL Server Management Studio query window and run it.

select 'create nonclustered index  IX_' + t.name + '_' + c.name + '_001'
    + ' ON ' + schema_name(t.schema_id) + '.' + t.name + '(' + c.Name + ');'
    from sys.columns c 
    join sys.tables t 
        on c.object_id = t.object_id
    where c.name like '%id%'
        and c.object_id not in 
            (
            select object_id from sys.index_columns
            ) 

You'll want to include other columns in these indexes to make sure your indexes are covering the queries, but this should get you started. Make sure the results are return in text, not grid.