To take script out of Rebuild and Statistics of Indexes on Databases

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