DECLARE @Database VARCHAR(255)
DECLARE @Sqlcmd VARCHAR(1000)
DECLARE @Test BIT
SET @Test = 1
DECLARE DatabaseCursor CURSOR FORWARD_ONLY READ_ONLY
FOR
SELECT name FROM master.dbo.sysdatabases
WHERE
NAME NOT IN ('master','model','msdb','tempdb','distrbution','ReportServer','ReportServerTempDB')
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sqlcmd=' USE '+ @Database +';
IF EXISTS (SELECT 1
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''DETAILED'') indexstats
WHERE indexstats.avg_fragmentation_in_percent > 25
)
BEGIN
EXEC sp_MSforeachtable @command1= ''ALTER INDEX ALL ON ? REBUILD''
'
+
' PRINT ''indexes rebuild successfully on database '+ @Database +
'!''
END
'
IF @Test = 1
BEGIN
SELECT @Sqlcmd
END
ELSE
BEGIN
EXEC (@Sqlcmd)
END
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor