create table tblIndexUsageInfo
(
 Sno int identity(1, 1)
,Dbname varchar(100)
,TableName varchar(100)
,IndexName varchar(300)
,Index_id int
,ConstraintType varchar(25)
,Type_desc varchar(100)
,IndexKeyColumn varchar(1000)
,IncludedColumn varchar(1000)
,user_seeks int
,user_scans int
,user_lookups int
,user_update int
,IndexUsage int
,IndexSizeKB int
,IndexUSageToSizeRatio decimal(10, 2)
)

go

create function Uf_GetIndexCol
(
 @index_id int
,@tableid int
,@isincluded bit
)
returns varchar(3000)
as 
begin
    return 
(
stuff( 
(select ',' + sc.name from sys.columns sc,
sys.index_columns sic,sys.indexes si
where sc.column_id=sic.column_id 
and si.index_id=sic.index_id 
and sc.object_id=sic.object_id 
and si.object_id=sic.object_id 
and sic.is_included_column=@isincluded 
and si.object_id=@tableid
and si.index_id=@index_id
for xml path('')),1,1,'')
)
end

go

create function Uf_GetIndexSize
(
 @index_id int
,@tableid int
)
returns float
as 
begin
    return (select (cast(reserved as float)*8192)/(1024) from sysindexes 
where indid=@index_id and id=@tableid)
end

go

create proc proc_FilltblIndexUsageInfo
as 
begin
    truncate table tblIndexUsageInfo
    insert  into tblIndexUsageInfo
            select distinct
                db_name(db_id()) DbName
               ,so.name as 'TableName'
               ,isnull(si.name, 'No Index') as IndexName
               ,si.index_id
               ,case when is_primary_key = 1 then 'Primary Key Constraint'
                     else 'Index'
                end ConstraintType
               ,si.type_desc
               ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 0) as IndexKeyColumn
               ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 1) as IncludedCols
               ,spi.user_seeks
               ,spi.user_scans
               ,spi.user_lookups
               ,spi.user_updates
               ,(user_seeks + user_scans + user_lookups + user_updates) as 'IndexUsage '
               ,dbo.Uf_GetindexSize(si.index_id, so.object_id) 'IndexSizeKB'
               ,cast((user_seeks + user_scans + user_lookups + user_updates) / dbo.Uf_GetindexSize(si.index_id, so.object_id) as decimal(10, 2)) as IndexUsagetoSizeRatio
            from
                sys.objects so
            inner join sys.indexes si
                on so.object_id = si.Object_id
            inner join sys.dm_db_index_usage_stats spi
                on spi.Object_id = so.Object_id
            inner join sys.index_columns sic
                on sic.object_id = si.object_id and sic.index_id = si.index_id
            inner join sys.columns sc
                on sc.Column_id = sic.column_id and sc.object_id = sic.object_id
            inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                on so.name = c.TABLE_NAME
            where
                so.type = 'u'
end

go

create table tblMostUsedIndexes
(
 Sno int identity(1, 1)
,TableName varchar(100)
,IndexName varchar(1000)
,Index_id int
,SchemaName varchar(100)
,TableId int
,IndexUsage int
,IndexUSageToSizeRatio decimal(10, 2)
)

go

create proc proc_InsertMostUsedIndexes
(
 @IndexUSageToSizeRatio decimal(10, 2)
,@indexusage int
)
as 
begin
    insert  into tblMostUsedIndexes
            select
                b.TableName
               ,b.IndexName
               ,(
                 select
                    index_id
                 from
                    sys.indexes
                 where
                    name = b.IndexName
                ) as Index_id
               ,ss.name as Schemaname
               ,object_id(tablename)
               ,IndexUsage
               ,IndexUSageToSizeRatio
            from
                tblIndexUsageInfo b
               ,sys.tables st
               ,sys.schemas ss
            where
                (
                 b.indexusage >= @indexUsage or IndexUSageToSizeRatio >= @IndexUSageToSizeRatio
                ) and st.name = tablename and st.schema_id = ss.schema_id and b.indexname not in (select
                                                                                                    indexname
                                                                                                  from
                                                                                                    tblMostUsedIndexes)
            group by
                b.indexname
               ,b.tablename
               ,ss.name
               ,b.IndexUSageToSizeRatio
               ,b.indexusage
end

go

create proc proc_RebuildSelectedIndexes
as 
begin
    set NOCOUNT on
/* Code to Rebuild or Reorganise index */
    declare
        @Schema varchar(200)
       ,@Tablename varchar(200)
    declare
        @indexName varchar(400)
       ,@Qry varchar(1000)
       ,@RecCount int
    declare
        @avg_frag decimal
       ,@dbid int
       ,@ObjectId int
    declare
        @IndexCount int
       ,@TotalRec int
       ,@Index_type varchar(50)
    declare
        @IndexRebuildCount int
       ,@IndexReorgCount int
       ,@IxOpr varchar(10)
    declare @index_id int
    set @IndexRebuildCount = 0
    set @IndexReorgCount = 0
    set @IxOpr = ''
    set @dbid = db_id()
    select
        @RecCount = sno
    from
        tblMostUsedIndexes
    set @TotalRec = @RecCount
    while (@RecCount > 0) 
        begin
            select
                @Schema = schemaname
               ,@TableName = TableName
               ,@ObjectId = tableid
               ,@index_id = index_id
            from
                tblMostUsedIndexes
            where
                sno = @RecCount

            select
                identity( int,1,1 ) as Sno
               ,a.[name] IndexName
               ,avg_fragmentation_in_percent as avg_frag
               ,type_desc
               ,a.index_id
            into
                #temp_2
            from
                sys.dm_db_index_physical_stats(@dbid, @objectid, @index_id, null, 'Limited') as b
            join 
                sys.indexes as a
                on a.object_id = b.object_id and a.index_id = b.index_id and a.index_id > 0 
            select
                @IndexCount = sno
            from
                #temp_2
            while (@IndexCount > 0) 
                begin
                    select
                        @avg_frag = avg_frag
                       ,@IndexName = indexname
                       ,@Index_Type = type_desc
                    from
                        #temp_2
                    where
                        sno = @IndexCount
                    if (@avg_frag <= 20) 
                        begin 
                            set @Qry = 'Alter index ' + @IndexName + ' ON ' + @Schema + '.' + @TableName + ' REORGANIZE;'
                            set @IndexReorgCount = @IndexReorgCount + 1
                            set @IxOpr = 'REORGANIZE'
                        end
                    if (@avg_frag > 20) 
                        begin 
                            set @Qry = 'Alter index ' + @IndexName + ' ON ' + @Schema + '.' + @TableName + ' REBUILD;'
                            set @IndexRebuildCount = @IndexRebuildCount + 1 
                            set @IxOpr = 'REBUILD'
                        end 
                    print @qry
                    execute(@qry)
                    set @IndexCount = @IndexCount - 1
                end
            drop table #temp_2
            set @RecCount = @RecCount - 1 
        end
    set NOCOUNT off
end

go

create table tblUnusedIndexes
(
 UnusedIndid int identity(1, 1)
,Schemaname varchar(100)
,tablename varchar(100)
,IndexName varchar(500)
,IndexUsage int
,IndexUsageToSizeRatio decimal(10, 2)
,IndexKey varchar(1000)
,IncludedCol varchar(1000)
,ConstraintType varchar(1000)
,IndexSizeKB int
,DropQry varchar(4000)
,IndexStatus varchar(20) default 'Active'
)

go

create procedure proc_FilltblUnusedIndexes --1,0
(
 @IndexUsageToSizeRatio decimal(10, 2)
,@indexusage int
)
as 
begin
    insert  into tblUnusedIndexes
            (
             Schemaname
            ,tablename
            ,IndexName
            ,IndexUsage
            ,IndexUsageToSizeRatio
            ,IndexKey
            ,IncludedCol
            ,ConstraintType
            ,IndexSizeKB
            ,DropQry
            )
-- Indexes that does not exist in sys.dm_db_index_usage_stats
            select
                ss.name SchemaName
               ,so.name as TableName
               ,isnull(si.name, 'NoIndex') as IndexName
               ,0 IndexUsage
               ,0 IndexUsageToSizeRatio
               ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 0) as IndexKey
               ,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 1) as IncludedCol
               ,case when is_primary_key = 1 then 'Primary Key Constraint'
                     else 'Index'
                end ConstraintType
               ,dbo.Uf_GetIndexSize(si.index_id, so.object_id) as IndexSizeInKB
               ,case when (is_primary_key = 1) then ('alter table ' + so.name + ' drop constraint ' + si.name)
                     else ('Drop Index ' + ss.name + '.' + so.name + '.' + si.name)
                end as DropQry
            from
                sys.objects so
            inner join sys.indexes si
                on so.object_id = si.Object_id
            inner join sys.schemas ss
                on ss.schema_id = so.schema_id
            where
                not exists ( select
                                *
                             from
                                sys.dm_db_index_usage_stats spi
                             where
                                si.object_id = spi.object_id and si.index_id = spi.index_id ) and so.type = 'U' and ss.schema_id <> 4 and si.index_id > 0 and si.name not in (
                select
                    indexname
                from
                    tblUnusedIndexes)
            union 
-- Indexes that doesn't satisfy the Indexusage criteria.
            select
                ss.name
               ,b.TableName
               ,b.IndexName
               ,b.IndexUsage
               ,b.IndexUSageToSizeRatio
               ,dbo.Uf_GetIndexCol(b.index_id, object_id(b.tablename), 0) as IndexKey
               ,dbo.Uf_GetIndexCol(b.index_id, object_id(b.tablename), 1) as IncludedCol
               ,b.ConstraintType
               ,dbo.Uf_GetIndexSize(b.index_id, object_id(b.tablename)) as IndexSizeInKB
               ,case b.ConstraintType
                  when 'Index' then ('Drop Index ' + ss.name + '.' + b.TableName + '.' + b.IndexName)
                  else ('alter table ' + b.TableName + ' drop constraint ' + b.IndexName)
                end DropQry
            from
                tblIndexUsageInfo b
               ,sys.tables st
               ,sys.schemas ss
            where
                (
                 b.indexusage <= @indexUsage or IndexUsageToSizeRatio <= @IndexUsageToSizeRatio
                ) and st.name = tablename and st.schema_id = ss.schema_id and b.indexname not in (select
                                                                                                    indexname
                                                                                                  from
                                                                                                    tblUnusedIndexes)
            group by
                b.indexname
               ,b.tablename
               ,ss.name
               ,ss.schema_id
               ,b.ConstraintType
               ,b.index_id
               ,b.indexusage
               ,b.IndexUsageToSizeRatio
end

go

create proc proc_DropUnusedIndex @UnusedIndID int
as 
begin
    declare @SqlStr varchar(4000)
    select
        @SqlStr = DropQry
    from
        tblunusedindexes
    where
        UnusedIndid = @UnusedIndID
    begin tran
    begin try
        execute(@SqlStr)
        update
            tblunusedindexes
        set 
            IndexStatus = 'Dropped'
        where
            UnusedIndID = @UnusedIndID
    end try
    begin catch
        select
            error_message() as ErrorMessage
        if @@TRANCOUNT > 0 
            rollback transaction ;
    end catch
    if @@TRANCOUNT > 0 
        commit transaction
    print 'Index dropped Successfully'
end

go

create table tblMissingIndexes
(
 Sno int identity(1, 1)
,DatabaseName varchar(100)
,tablename varchar(200)
,Significance decimal(10, 0)
,CreateIndexStatement varchar(8000)
,Status varchar(20) default ('NotCreated')
)

go

create procedure proc_FindMissingIndexes
as 
begin
    insert  into tblMissingIndexes
            (
             DatabaseName
            ,tablename
            ,Significance
            ,CreateIndexStatement
            )
            select
                db_name(sid.database_id)
               ,sid.statement
               ,(avg_total_user_cost * avg_user_impact) * (user_scans + user_seeks) as Significance
               ,dbo.fn_CreateIndexStmt(sid.statement, sid.equality_columns, sid.inequality_columns, sid.included_columns)
            from
                sys.dm_db_missing_index_details sid
               ,sys.dm_db_missing_index_group_stats sigs
               ,sys.dm_db_missing_index_groups sig
            where
                sig.index_group_handle = sigs.group_handle and sid.index_handle = sig.index_handle
            order by
                significance desc
end

go

create function fn_CreateIndexStmt
(
 @statement varchar(1000)
,@Equalitycols varchar(1000)
,@InEqualitycols varchar(1000)
,@Includedcols varchar(1000)
)
returns varchar(5000)
as 
begin
    declare
        @str varchar(5000)
       ,@tablename varchar(100)
    set @tablename = substring(substring(@statement, charindex('.', @statement) + 1, len(@statement)),
                               charindex('.', substring(@statement, charindex('.', @statement) + 1, len(@statement))) + 1,
                               len(substring(@statement, charindex('.', @statement) + 1, len(@statement))))
    set @Includedcols = replace(replace(@Includedcols, ']', ''), '[', '')
    set @Equalitycols = replace(replace(replace(@Equalitycols, ']', ''), ', ', '_'), '[', '')
    set @InEqualitycols = replace(replace(replace(@InEqualitycols, ']', ''), ', ', '_'), '[', '')
    set @str = 'Create Index Ix_' + replace(replace(@tablename, ']', ''), '[', '') 
    set @str = case when @Equalitycols is null then @str
                    else (@str + '_' + isnull(@Equalitycols, ''))
               end
    set @str = case when @InEqualitycols is null then @str
                    else (@str + '_' + isnull(@InEqualitycols, ''))
               end 
    set @str = @str + ' ON ' + @statement + '(' + case when @Equalitycols is null then ''
                                                       else replace(isnull(@Equalitycols, ''), '_', ',')
                                                  end + case when @InEqualitycols is null then ''
                                                             else ',' + replace(isnull(@InEqualitycols, ''), '_', ',')
                                                        end + ')'
    set @str = case when @Includedcols is null then @str
                    else @str + 'Include (' + isnull(@Includedcols, '') + ')'
               end
    return @str
end

go

create procedure proc_CreateMissingIndexes
    @significance decimal(10, 0)
as 
begin
    declare
        @Count int
       ,@SqlStr varchar(8000)
    set @SqlStr = ''
    select
        identity( int,1,1 ) as Sno
       ,CreateIndexStatement
    into
        #temp
    from
        tblmissingindexes
    where
        significance > @significance
    select
        @count = count(*)
    from
        #temp
    while (@count >= 0) 
        begin
            select
                @SqlStr = CreateIndexStatement
            from
                #temp
            where
                sno = @count
            update
                tblmissingindexes
            set 
                Status = 'Created'
            where
                sno = @count
            exec(@sqlStr)
            set @count = @Count - 1
        end
end