Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor to reindex all tables in the Northwind database with over 35% logical fragmentation.

-- SQL Server dbreindex fragmented indexes - index rebuild script
-- SQL Server REBUILD indexes - rebuild all indexes
USE Northwind;
GO
-- Create temporary table to hold meta data information about indexes
CREATE TABLE #IndexFragmentation (
  ObjectName     CHAR(255),
  ObjectId       INT,
  IndexName      CHAR(255),
  IndexId        INT,
  Lvl            INT,
  CountPages     INT,
  CountRows      INT,
  MinRecSize     INT,
  MaxRecSize     INT,
  AvgRecSize     INT,
  ForRecCount    INT,
  Extents        INT,
  ExtentSwitches INT,
  AvgFreeBytes   INT,
  AvgPageDensity INT,
  ScanDensity    DECIMAL,
  BestCount      INT,
  ActualCount    INT,
  LogicalFrag    DECIMAL,
  ExtentFrag     DECIMAL)
 
INSERT #IndexFragmentation
EXEC( 'DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES')
 
DELETE #IndexFragmentation
WHERE  left(ObjectName,3) = 'sys'
 
-- SQL cursor
-- SQL dynamic SQL
-- SQL while loop
DECLARE  @Table      SYSNAME,
         @DynamicSQL VARCHAR(512)
 
DECLARE  @objectid INT,
         @indexid  INT
 
DECLARE  @Fragmentation    DECIMAL,
         @MaxFragmentation DECIMAL
 
SET @MaxFragmentation = 35.0
 
DECLARE curIndexFrag CURSOR  FOR
SELECT   ObjectName,
         LogicalFrag = max(LogicalFrag)
FROM     #IndexFragmentation
WHERE    LogicalFrag >= @MaxFragmentation
         AND indexid != 0
         AND indexid != 255
GROUP BY ObjectName
 
OPEN curIndexFrag
 
FETCH NEXT FROM curIndexFrag
INTO @Table,
     @Fragmentation
 
WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @DynamicSQL = 'DBCC DBREINDEX (' + QUOTENAME(RTRIM(@Table)) + ', '''', 80)'
    
    PRINT @DynamicSQL
    
    EXEC( @DynamicSQL)
    
    FETCH NEXT FROM curIndexFrag
    INTO @Table,
         @Fragmentation
  END
 
CLOSE curIndexFrag
 
DEALLOCATE curIndexFrag
GO
/* Messages
 
DBCC DBREINDEX ([Order Details], '', 80)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
DBCC DBREINDEX ([Orders], '', 80)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
*/
-- Cleanup
DROP TABLE #IndexFragmentation

GO

------------