This script gets the fragmentation level of your indexes with over 1,000 pages (that way, it skips small tables)

If the fragmentation level is less than 40%, it will reorganize the index.

If the fragmentation level is more than 40%, it will rebuild the index.

The script also presents an option to do a "dry-run", to only print the statements.

/*
DESCRIPTION:
Reindex or Reorg all indexes based on fragmentation threshold
 
CONFIGURATION

Top: enter name of database



Compatibility list:
MSSQL2005
MSSQL2008

Does not work
MSSQL2000
 */
 
USE <database name>
GO

Declare @TestRun int;

Set @TestRun = 1   --- Set @TestRun = 1 to execute the rebuild  Set @TestRun = 0 to only print the statements.


 
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);  
DECLARE @dbid smallint;  
 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function  
-- and convert object and index IDs to names.  
 
SET @dbid = DB_ID();  
 
SELECT  
    [object_id] AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag, page_count  
 
INTO #work_to_do  
 
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')  
 
WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation  
AND index_id > 0 -- Ignore heaps  
AND page_count > 1000; -- Ignore small tables  
 
-- Declare the cursor for the list of partitions to be processed.  
 
DECLARE partitions CURSOR FOR

SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;  
 
-- Open the cursor.  
 
OPEN partitions;  
 
-- Loop through the partitions.  
 
WHILE (1=1)  
BEGIN  
FETCH NEXT  
FROM partitions  
INTO @objectid, @indexid, @partitionnum, @frag;  
 
IF @@FETCH_STATUS < 0 BREAK;  
 
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
FROM sys.objects AS o  
JOIN sys.schemas as s ON s.schema_id = o.schema_id  
WHERE o.object_id = @objectid;  
 
SELECT @indexname = QUOTENAME(name)  
FROM sys.indexes  
WHERE object_id = @objectid AND index_id = @indexid;  
 
SELECT @partitioncount = count (*)  
FROM sys.partitions  
WHERE object_id = @objectid AND index_id = @indexid;  
 
-- 40 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
 
IF @frag < 40.0  
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
 
IF @frag >= 40.0  
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=ON)';  
 
---IF @partitioncount > 1  
---SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  

IF @TestRun = 1
EXEC (@command);  

PRINT N'Executed: ' + @command;  
END  
 
-- Close and deallocate the cursor.  
 
CLOSE partitions;  
DEALLOCATE partitions;  
 
-- Drop the temporary table.  
 
DROP TABLE #work_to_do;  
GO