The fixed code... I am not sure if it runs but I can create all the objects.
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