Hello, I found this somewhere and tweaked it to meet my needs.-- Create needed tableUSE YourDatabaseGOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fragmentation]') AND type in (N'U')) DROP TABLE Fragmentation;CREATE TABLE [dbo].[Fragmentation]( DBName varchar(128), TableName varchar(128), ExecuteCode varchar(1000), avg_frag_percent decimal(18, 4) );GOTRUNCATE TABLE YourDatabase.dbo.Fragmentation; DECLARE @strSQL NVARCHAR(2000) SET @strSQL = 'IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'', ' + '''ReportServer$DB'', ''distribution'') RETURN; ' + 'USE ?; ' + 'DECLARE @Did INT; ' + 'SELECT @Did = dbid FROM sys.sysdatabases WHERE name = DB_NAME(); ' + 'INSERT INTO YourDatabase.dbo.Fragmentation ' + 'SELECT ''?'' AS DBName, t.name AS TableName, ' + 'CASE WHEN avg_fragmentation_in_percent < 30 ' + 'THEN ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REORGANIZE; --'' ' + 'ELSE ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REBUILD; --'' ' + 'END AS ExecuteCode, avg_fragmentation_in_percent ' + 'FROM sys.dm_db_index_physical_stats (@Did, NULL, NULL , NULL, N''LIMITED'') d ' + 'INNER JOIN sysindexes s ON OBJECT_ID = s.id and d.index_id = s.indid ' + 'INNER JOIN sys.tables t ON s.id = t.object_id ' + 'WHERE avg_fragmentation_in_percent > 30 And ' + 's.name IS NOT NULL And page_count > 1000 ' + 'ORDER BY DBName, s.name DESC; '--print @strSQL EXECUTE sp_MSForEachdB @strSQLSELECT * FROM YourDatabase.dbo.Fragmentation ORDER BY DBName, TableName, ExecuteCode DESC;
djj