DECLARE @String NVARCHAR(MAX);
USE Databse Name;
SELECT @String
=
(
SELECT 'ALTER INDEX [' + dbindexes.[name] + '] ON [' + db.name + '].[' + dbschemas.[name] + '].[' + dbtables.[name]
+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' + CHAR(10) AS [text()]
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
INNER JOIN sys.databases AS db
ON db.database_id = indexstats.database_id
WHERE dbindexes.name IS NOT NULL
AND indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 10
ORDER BY indexstats.page_count DESC
FOR XML PATH('')
);
EXEC (@String);