-- =====================================================================
-- Walter Verhoeven
-- Database integration ZFS
-- Create missing indexes for SQL server 2005 or later
-- based on server statistics collected by SQL servers
-- execution plan creation engine (as of last SQL server service start)
-- =====================================================================
declare @OnlyDrop varchar(3)
set @OnlyDrop = 'No'--Yes or No are possible
DECLARE index_cursor CURSOR
READ_ONLY
FOR select
db.name as [database],
OBJECT_NAME( d.object_id,d.database_id ) as ObjectName,
d.equality_columns as [Columns1],d.inequality_columns as [Columns2],
d.included_columns as [covered]
from sys.dm_db_missing_index_details as D
join sys.dm_db_missing_index_groups as g on g.index_handle = d.index_handle
join sys.databases db on db.database_id= d.database_id
--where d.Database_id >4
--where d.Database_id = db_id('<database name>')
order by 1,2,3
DECLARE @IndexName nvarchar(400), @schema sysname, @sql nvarchar(4000),@DatabaseOld sysname, @Database sysname,@table sysname,@Column1 varchar(8000), @Column2 varchar(8000), @covering varchar(8000)
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @Database,@table,@Column1,@Column2,@covering
WHILE (@fetch_status <> -1)
BEGIN
IF (@fetch_status <> -2)
BEGIN
if @DatabaseOld is null or @DatabaseOld!= @Database
begin
print 'USE '+@Database
print 'go'
set @DatabaseOld = @Database
end
set @sql = N'select @schema= TABLE_SCHEMA from '+@Database+'.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= @table'
exec sp_executesql @sql,N'@table sysname, @schema sysname output',@table,@schema output
if @Column1 is not null
begin
set @IndexName = 'IX_'+@table+'_'+@Column1 + case when @covering is not null then '_COVERING_'+@covering else '' end
set @IndexName = Replace(@IndexName,'[','')
set @IndexName = Replace(@IndexName,']','')
set @IndexName = Replace(@IndexName,' ','')
set @IndexName = Replace(@IndexName,',','_')
set @IndexName = substring(@IndexName,1,128)
print 'IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'''+@IndexName+''')'
print ' DROP INDEX '+@IndexName+' ON '+@schema+'.'+@table
print 'GO'
if @OnlyDrop='No'
begin
print 'CREATE NONCLUSTERED INDEX ' +@IndexName
print 'ON '+@schema+'.'+@table+' ('+@Column1+')'
if @covering is not null print 'INCLUDE ('+@covering+')'
print 'WITH (FILLFACTOR = 80,PAD_INDEX = ON);'
print 'GO'
end
end
if @Column2 is not null
begin
set @IndexName = 'IX_'+@table+'_'+@Column2 + case when @covering is not null then '_COVERING_'+@covering else '' end
set @IndexName = Replace(@IndexName,'[','')
set @IndexName = Replace(@IndexName,']','')
set @IndexName = Replace(@IndexName,' ','')
set @IndexName = Replace(@IndexName,',','_')
set @IndexName = substring(@IndexName,1,128)
print 'IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'''+@IndexName+''')'
print ' DROP INDEX '+@IndexName+' ON '+@schema+'.'+@table
print 'GO'
if @OnlyDrop='No'
begin
print 'CREATE NONCLUSTERED INDEX ' +@IndexName
print 'ON '+@schema+'.'+@table+' ('+@Column2+')'
if @covering is not null print 'INCLUDE ('+@covering+')'
print 'WITH (FILLFACTOR = 80,PAD_INDEX = ON);'
print 'GO'
end
end
END
FETCH NEXT FROM index_cursor INTO @Database,@table,@Column1,@Column2,@covering
END
CLOSE index_cursor
DEALLOCATE index_cursor
GO