You can use something like this to Rebuil or Reorg the indexes debending of the fragmentation grade.
USE master
DECLARE @DBName varchar(100), @DBIDS int, @Row int, @Rows int
DECLARE @Databases_Var TABLE (Row int, DBName varchar(100), DBIDS int)
INSERT INTO @Databases_Var
SELECT ROW_NUMBER() OVER(ORDER BY Database_ID ASC) AS Row, name, database_id
FROM sys.databases
WHERE State = 0
--AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND name = 'wi381502_AppsMexico'
SET @Rows = @@ROWCOUNT
SET @Row = 1
WHILE (@Row <= @Rows)
BEGIN
SELECT @DBName = DBName, @DBIDS = DBIDS FROM @Databases_Var WHERE Row = @Row
BEGIN
EXEC(' USE ' + @DBName + '
SELECT DB_NAME()
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 80
DECLARE @report_only bit SET @report_only = 1
DECLARE @page_count_thresh smallint SET @page_count_thresh = 500
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 @page_count int
DECLARE @command nvarchar(4000)
DECLARE @intentions nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float,
page_count int
)
SET NOCOUNT ON
IF EXISTS(SELECT Name FROM sys.databases WHERE name = ''' + @DBName + ''' and recovery_model <> 3)
BEGIN
CHECKPOINT
ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT
WAITFOR DELAY ''00:00:02''
END
INSERT INTO @table_var
SELECT [object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag,
[page_count] AS page_count
FROM
sys.dm_db_index_physical_stats (' + @DBIDS + ', NULL, NULL , NULL, ''LIMITED'')
WHERE [avg_fragmentation_in_percent] > @reorg_frag_thresh
AND page_count > @page_count_thresh
AND index_id > 0
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
OPEN partitions
WHILE (1=1) BEGIN
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag, @page_count
IF @@FETCH_STATUS < 0 BREAK
SELECT @objectname = QUOTENAME(o.[name]),
@schemaname = QUOTENAME(s.[name])
FROM sys.objects AS o WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK) ON s.[schema_id] = o.[schema_id]
WHERE o.[object_id] = @objectid
SELECT @indexname = QUOTENAME([name])
FROM sys.indexes WITH (NOLOCK)
WHERE [object_id] = @objectid AND [index_id] = @indexid
SELECT @partitioncount = count (*)
FROM sys.partitions WITH (NOLOCK)
WHERE [object_id] = @objectid AND [index_id] = @indexid
SET @intentions =
@schemaname + N''.'' +
@objectname + N''.'' +
@indexname + N'':'' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), '' '', ''='') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N'' FRAGMENTATION: '' + CAST(@frag AS nvarchar) + N''%'' + CHAR(13) + CHAR(10) +
N'' PAGE COUNT: '' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N'' OPERATION: REORGANIZE'' + CHAR(13) + CHAR(10)
SET @command =
N''ALTER INDEX '' + @indexname +
N'' ON '' + @schemaname + N''.'' + @objectname +
N'' REORGANIZE; '' +
N'' UPDATE STATISTICS '' + @schemaname + N''.'' + @objectname +
N'' '' + @indexname + '';''
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N'' OPERATION: REBUILD'' + CHAR(13) + CHAR(10)
SET @command =
N''ALTER INDEX '' + @indexname +
N'' ON '' + @schemaname + N''.'' + @objectname +
N'' REBUILD''
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N'' PARTITION: '' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N'' FILL FACTOR: '' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N'' WITH (FILLFACTOR = '' + CAST(@fill_factor AS nvarchar) + '')''
END
IF @report_only = 0
BEGIN
SET @intentions = @intentions + N'' EXECUTING: '' + @command
PRINT @intentions
EXEC (@command)
END
ELSE
BEGIN
PRINT @intentions
END
PRINT @command
END
CLOSE partitions
DEALLOCATE partitions
SET NOCOUNT OFF
')
END
SET @Row = @Row + 1
END
GO
