-- Ensure you are in the correct database context
USE #DBASENAME#;
GO
DROP TABLE IF EXISTS #FragmentationDetails;
-- Temporary table to store fragmentation details
CREATE TABLE #FragmentationDetails (
SchemaName NVARCHAR(255),
TableName NVARCHAR(255),
IndexName NVARCHAR(255),
Fragmentation FLOAT
);
-- Insert fragmentation details into the temporary table
INSERT INTO #FragmentationDetails
SELECT
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
ps.avg_fragmentation_in_percent AS Fragmentation
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE
ps.index_id > 0 and ps.avg_fragmentation_in_percent<>0
ORDER BY t.name;
-- Cursor to loop through the fragmentation details
DECLARE cur CURSOR FOR
SELECT
SchemaName,
TableName,
IndexName,
Fragmentation
FROM
#FragmentationDetails;
OPEN cur;
-- Declare variables for the table and index
DECLARE @tableName NVARCHAR(255)
DECLARE @indexName NVARCHAR(255)
DECLARE @schemaName NVARCHAR(255)
DECLARE @fragmentation FLOAT
DECLARE @sql NVARCHAR(MAX)
FETCH NEXT FROM cur INTO @schemaName, @tableName, @indexName, @fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
-- Construct the SQL statement based on fragmentation level
IF @fragmentation BETWEEN 30 AND 50
BEGIN
SET @sql = N'ALTER INDEX ' + QUOTENAME(@indexName) + N' ON ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' REORGANIZE;';
END
ELSE IF @fragmentation > 50
BEGIN
SET @sql = N'ALTER INDEX ' + QUOTENAME(@indexName) + N' ON ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' REBUILD WITH (ONLINE = ON);';
END
ELSE
BEGIN
-- If fragmentation is less than 30%, no action is taken
SET @sql = NULL;
END
-- Print the SQL statement for logging/debugging
IF @sql IS NOT NULL
BEGIN
PRINT @sql;
-- Execute the SQL statement
EXEC sp_executesql @sql;
END
FETCH NEXT FROM cur INTO @schemaName, @tableName, @indexName, @fragmentation;
END
-- Clean up
CLOSE cur;
DEALLOCATE cur;
DROP TABLE #FragmentationDetails;
GO