Index defrag,rebuild and reorganize

Question

Hi Team,

I am looking for a script, which can provide defrag status and generate rebuild and reorganize scripts ALTER statement in Azure SQL DB.

I found lot of scripts but i didn't find the script with ALTER statements.
Please help me.

Answer 1

Please let me know if the script below works:

DECLARE @SQL1 varchar(200);   
DECLARE @Schema as nvarchar(50);  
DECLARE @Table as nvarchar(50);  
DECLARE @Index as nvarchar(200);  
DECLARE @avg_fragmentation_in_percent as nvarchar(50);  
DECLARE @page_count as nvarchar(50);  
  
  
DECLARE IndexCursor CURSOR STATIC  
FOR  
SELECT dbschemas.[name] as 'Schema',  
dbtables.[name] as 'Table',  
dbindexes.[name] as 'Index',  
indexstats.avg_fragmentation_in_percent,  
indexstats.page_count  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats  
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]  
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]  
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]  
AND indexstats.index_id = dbindexes.index_id  
WHERE indexstats.database_id = DB_ID() and indexstats.avg_fragmentation_in_percent > 10 ---this will include all fragmented index  
and indexstats.page_count > 2000----to filter out small indexes  
OPEN IndexCursor  
FETCH NEXT FROM IndexCursor INTO @Schema, @Table, @Index, @avg_fragmentation_in_percent, @page_count;  
WHILE @@FETCH_STATUS = 0  
BEGIN     
If (CAST(@avg_fragmentation_in_percent as decimal) >= 30)  
BEGIN  
SET @SQL1 = N'ALTER INDEX ALL '  + N'ON ' + quotename(@Schema) + '.' + quotename(@Table) + N' REBUILD;'   
PRINT @SQL1  
END  
ELSE  
BEGIN  
SET @SQL1 = N'ALTER INDEX ALL '  + N'ON ' + quotename(@Schema) + '.' + quotename(@Table) + N' REORGANIZE;'  
PRINT @SQL1  
END  
/*waitfor delay '00:00:10'*/  
FETCH NEXT FROM IndexCursor INTO @Schema, @Table, @Index, @avg_fragmentation_in_percent, @page_count;  
END  
  
CLOSE IndexCursor  
DEALLOCATE IndexCursor  

Answer 2

Consider Ola Halengren's SQL Server Maintenance Solution for Azure SQL Database index maintenance as well as Managed Instance and on-prem. This will rebuild/reorganize indexes based on configurable thresholds and options.