SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @onlineflag varchar(3);
DECLARE @MsgBody NVARCHAR(MAX) ;
Declare @sqlcommand varchar(max);
DECLARE @objectid_var varchar(30);
Declare @allow_page_locks int;
Create table #work_to_do
(objectid int,
indexid int,
partitionnum int,
indexdepth tinyint ,
index_level tinyint,
frag float,
onlineflag varchar(3)
)
Create table #work_to_do1
(
sqlcommand varchar(max)
)
DECLARE table_cursor
CURSOR FOR select id from sys.sysobjects
where type='u'
-- Open the cursor.
OPEN table_cursor;
FETCH NEXT
FROM table_cursor
INTO @objectid
WHILE (1 = 1)
BEGIN;
IF @@FETCH_STATUS < 0 BREAK;
SET @objectid_var=convert(varchar(30),@objectid)
SET @sqlCOMMAND='INSERT INTO #work_to_do
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
index_depth AS indexdepth,
index_level AS indexlevel,
avg_fragmentation_in_percent AS frag,''ON''
FROM sys.dm_db_index_physical_stats (DB_ID(),'+ @objectid_var + ', NULL, NULL , NULL)
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0';
--print @sqlCOMMAND
exec (@sqlCOMMAND)
FETCH NEXT
FROM table_cursor
INTO @objectid;
END;
-- Close and deallocate the cursor.
CLOSE table_cursor;
DEALLOCATE table_cursor;
--select * from #work_to_do
Update #work_to_do
set ONLINEflag='OFF'
from sys.indexes A,INFORMATION_SCHEMA.COLUMNS c,#work_to_do B
where a.type_desc = 'CLUSTERED'
and (DATA_TYPE IN ('text', 'ntext', 'image') OR
CHARACTER_MAXIMUM_LENGTH = -1)
and TABLE_NAME=OBJECT_NAME(A.object_id)
AND A.object_id=B.objectid
AND A.index_id=B.indexid
Update #work_to_do
set ONLINEflag='OFF'
from #work_to_do A,sys.indexes i
JOIN sys.index_columns ic
ON i.object_id = ic.object_id
JOIN INFORMATION_SCHEMA.COLUMNS c
ON ic.column_id = c.ORDINAL_POSITION
WHERE c.TABLE_NAME = OBJECT_NAME(i.object_id) AND
ic.is_included_column = 1 AND
(c.DATA_TYPE IN ('text', 'ntext', 'image') OR c.CHARACTER_MAXIMUM_LENGTH = -1)
AND A.objectid=I.object_id
AND A.indexid=I.index_id
SET @MsgBody =
N'<H1>Fragmented Indexes</H1>' +
N'<table border="1">' +
N'<tr><th>DBNAME</th><th>Table_Name</th><th>Index_Name</th>' +
N'<th>Fragmentation</th><th>Index Operation</th><th>Online_Flag</th></tr>' +
CAST ( ( SELECT td = db_name(), '',
td = object_name(objectid), '',
td = name, '',
td = convert(varchar(8),frag), '',
td = Case When frag>=30.0 Then 'REBUILD'
When frag >= 10 and frag < 30.0 Then 'REORGANIZE' END,'',
td = onlineflag, ''
FROM #work_to_do a,sys.sysindexes b
where a.objectid=b.id
and a.indexid=b.indid
order by frag,onlineflag
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
/*EXEC msdb.dbo.sp_send_dbmail
@recipients='xxxxxxxx@xxxxx.com;',
@subject = 'Fragmented Index Report',
@body = @MsgBody ,
@body_format = 'HTML' ;*/
DECLARE Index_cursor
CURSOR FOR select object_name(objectid),name,frag,onlineflag,allow_page_locks
FROM #work_to_do a,sys.indexes b
where a.objectid=b.object_id
and a.indexid=b.index_id
-- Open the cursor.
OPEN Index_cursor;
FETCH NEXT
FROM Index_cursor
INTO @objectname,@indexname,@frag,@onlineflag,@allow_page_locks
WHILE (1 = 1)
BEGIN;
IF @@FETCH_STATUS < 0 BREAK;
if @frag>=30
seT @sqlcommand='ALTER INDEX ['+@indexname+'] ON [dbo].['+@objectname+'] REBUILD WITH (ONLINE='+@onlineflag +')'
ELSE
If @allow_page_locks=1
seT @sqlcommand='ALTER INDEX ['+@indexname+'] ON [dbo].['+@objectname+'] REORGANIZE'
Else
seT @sqlcommand='ALTER INDEX ['+@indexname+'] ON [dbo].['+@objectname+'] REBUILD WITH (ONLINE='+@onlineflag +')'
insert into #work_to_do1
select (@sqlCOMMAND)
seT @sqlcommand='UPDATE STATISTICS [dbo].['+@objectname+'] ['+@indexname+'] WITH FULLSCAN'
insert into #work_to_do1
select (@sqlCOMMAND)
FETCH NEXT
FROM Index_cursor
INTO @objectname,@indexname,@frag,@onlineflag,@allow_page_locks
END;
-- Close and deallocate the cursor.
CLOSE Index_cursor;
DEALLOCATE Index_cursor;
/*********************/
DECLARE cmd_cursor
CURSOR FOR select distinct sqlcommand
FROM #work_to_do1
-- Open the cursor.
OPEN cmd_cursor;
FETCH NEXT
FROM cmd_cursor
INTO @sqlcommand
WHILE (1 = 1)
BEGIN;
IF @@FETCH_STATUS < 0 BREAK;
print (@sqlCOMMAND)
FETCH NEXT
FROM cmd_cursor
INTO @sqlcommand
END;
-- Close and deallocate the cursor.
CLOSE cmd_cursor;
DEALLOCATE cmd_cursor;
DROP TABLE #work_to_do
DROP TABLE #work_to_do1
