select [TABLE] = tbl.name ,[SCHEMA_ID] = sch.name ,[INDEX] = idx.name ,[IDX_NAME] = 'IX_' + col.name ,[COLUMN] = col.name ,[FK] = fk.name ,[CREATE_SQL] = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + sch.name + '.[' + tbl.[name] + ']'') AND name = N''IX_' + col.[name] + ''') ' + 'CREATE NONCLUSTERED INDEX [IX_' + col.[name] + '] ON ' + sch.name + '.[' + tbl.[name] + ']( [' + col.[name] + '] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' ,[DROP_SQL] = CASE WHEN idx.name IS NOT NULL THEN 'DROP INDEX [' + idx.name + '] ON ' + sch.name + '.[' + tbl.name + ']' ELSE '' END from sys.tables as tbl inner join sys.foreign_keys as fk on tbl.object_id = fk.parent_object_id inner join sys.columns as col on tbl.object_id = col.object_id inner join sys.foreign_key_columns as fkcol on fk.object_id = fkcol.constraint_object_id and col.column_id = fkcol.parent_column_id inner Join sys.schemas sch on fk.schema_id = sch.schema_id left outer join ( select idx.name, idx.object_id, idxcol.column_id, idx.is_primary_key from sys.indexes as idx join sys.index_columns as idxcol on idx.index_id = idxcol.index_id and idx.object_id = idxcol.object_id where idx.is_primary_key = 0 ) as idx on tbl.object_id = idx.object_id and fkcol.parent_column_id = idx.column_id order by tbl.name go
Monday, January 9, 2017
Managing indexes for foreign keys
Having an index for every foreign key is usually a good thing. If you need to figure out if you have any this script might help you out. It finds all existing indexes for foreign keys and creates drop statements for them. It also creates index create statements for all foreign keys including the ones that already existed. So, by dropping all existing and using the create statements you get consistently named indexes for all foreign keys in all your tables.