--Auto Rebuild Indexes those fragmentation are more than 30%
--Change the db_id "DataTransfer" to yours
DECLARE @v_TabName NVARCHAR(500);
DECLARE @v_IndexName NVARCHAR(500);
DECLARE @v_SQL NVARCHAR(MAX);
DECLARE @v_SQL2 NVARCHAR(MAX);
DECLARE @v_Indexes_Count INT;
DECLARE Batch_Search CURSOR
FAST_FORWARD
FOR
select name from sys.sysobjects where xtype = 'U';
OPEN Batch_Search;
FETCH NEXT FROM Batch_Search
INTO @v_TabName
WHILE @@FETCH_STATUS=0
BEGIN
SET @v_SQL = 'select distinct name into ##Indexes from sys.dm_db_index_physical_stats(db_id(''DataTransfer''),
object_id(N''' + @v_TabName + '''), null,null,''Sampled'') as s,
sys.indexes as i
where i.object_id = s.object_id
and i.name is not null
and i.index_id = s.index_id
and s.avg_fragmentation_in_percent > 30;'
EXEC (@v_SQL)
select @v_Indexes_Count = COUNT(*) from ##Indexes;
if (@v_Indexes_Count = 0)
drop table ##Indexes;
if (@v_Indexes_Count > 0)
begin
DECLARE Batch_Search_Indexes CURSOR
FAST_FORWARD
FOR
select name from ##Indexes;
OPEN Batch_Search_Indexes;
FETCH NEXT FROM Batch_Search_Indexes
INTO @v_IndexName
WHILE @@FETCH_STATUS=0
BEGIN
SET @v_SQL2 = 'ALTER INDEX ' + @v_IndexName + ' ON ' + @v_TabName + ' REBUILD;'
EXEC(@v_SQL2)
FETCH NEXT FROM Batch_Search_Indexes
INTO @v_IndexNameS
END
CLOSE Batch_Search_Indexes;
DEALLOCATE Batch_Search_Indexes;
drop table ##Indexes;
end;
FETCH NEXT FROM Batch_Search
INTO @v_TabName
END
CLOSE Batch_Search;
DEALLOCATE Batch_Search;
--Auto Rebuild Indexes those fragmentation are more than 30%
--Change the db_id "DataTransfer" to yours
DECLARE @v_TabName NVARCHAR(500);
DECLARE @v_IndexName NVARCHAR(500);
DECLARE @v_SQL NVARCHAR(MAX);
DECLARE @v_SQL2 NVARCHAR(MAX);
DECLARE @v_Indexes_Count INT;
DECLARE Batch_Search CURSOR
FAST_FORWARD
FOR
select name from sys.sysobjects where xtype = 'U';
OPEN Batch_Search;
FETCH NEXT FROM Batch_Search
INTO @v_TabName
WHILE @@FETCH_STATUS=0
BEGIN
SET @v_SQL = 'select distinct name into ##Indexes from sys.dm_db_index_physical_stats(db_id(''DataTransfer''),
object_id(N''' + @v_TabName + '''), null,null,''Sampled'') as s,
sys.indexes as i
where i.object_id = s.object_id
and i.name is not null
and i.index_id = s.index_id
and s.avg_fragmentation_in_percent > 30;'
EXEC (@v_SQL)
select @v_Indexes_Count = COUNT(*) from ##Indexes;
if (@v_Indexes_Count = 0)
drop table ##Indexes;
if (@v_Indexes_Count > 0)
begin
DECLARE Batch_Search_Indexes CURSOR
FAST_FORWARD
FOR
select name from ##Indexes;
OPEN Batch_Search_Indexes;
FETCH NEXT FROM Batch_Search_Indexes
INTO @v_IndexName
WHILE @@FETCH_STATUS=0
BEGIN
SET @v_SQL2 = 'ALTER INDEX ' + @v_IndexName + ' ON ' + @v_TabName + ' REBUILD;'
EXEC(@v_SQL2)
FETCH NEXT FROM Batch_Search_Indexes
INTO @v_IndexNameS
END
CLOSE Batch_Search_Indexes;
DEALLOCATE Batch_Search_Indexes;
drop table ##Indexes;
end;
FETCH NEXT FROM Batch_Search
INTO @v_TabName
END
CLOSE Batch_Search;
DEALLOCATE Batch_Search;