Listing 1. Excerpt of uspBuildFraglist Stored Procedure

-- Loop through all the tables in the database
FETCH NEXT
   FROM TableList
   INTO @tablename, @Table_Owner

WHILE 1=1 BEGIN
   FETCH NEXT
      FROM TableList
      INTO @tablename, @Table_Owner
   IF @@FETCH_STATUS <> 0 BEGIN
	break
   END

-- Do the showcontig of all indexes of the table
   SET @TableName = RTRIM(@TableName)
   SELECT @LastScanTime = MAX(LoadTime) 
	FROM AdminData.dbo.DBCCStatistics 
	WHERE DatabaseName = DB_Name() AND
	      ObjectName = @TableName

BEGIN CALLOUT A  
 IF @LastScanTime IS NULL OR (DATEDIFF(dd,
    @LastScanTime, GetDate()) > 1) BEGIN
   SET @strTmp = 'Processing ' + @DBName + '.' + @Table_Owner + '.'
     + @TableName
       PRINT @strTmp
END CALLOUT A

         INSERT INTO #fraglist (ObjectName, ObjectId, IndexName,
           IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize,
           AvgRecSize, ForRecCount, Extents, ExtentSwitches,
           AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount,
           ActualCount, LogicalFrag, ExtentFrag)
         EXEC ('USE [' + @DBName + '] DBCC SHOWCONTIG
          (''' + @tablename + ''') WITH TABLERESULTS,
            All_Indexes')

         IF @@RowCount > 0 BEGIN
           INSERT AdminData.dbo.DBCCStatistics
            (DatabaseName, LoadTime, OwnerName, ObjectName, ObjectId,
            IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize,
            MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches,
            AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount,
            ActualCount, LogicalFrag, ExtentFrag)

              SELECT @DBName, LoadTime, @Table_Owner, ObjectName,
                ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows,
                MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents,
                ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity,
                BestCount, ActualCount, LogicalFrag, ExtentFrag
               FROM #fraglist

               Truncate Table #fraglist
         END
BEGIN CALLOUT B
           IF EXISTS (SELECT blocked from master.dbo.sysprocesses (nolock)
                 where blocked > 0 and blocked = @@Spid)
                    waitfor delay '000:00:30'
           ELSE
                    waitfor delay '000:00:05'
   END ELSE BEGIN
           SET @strTmp = 'Skipping ' + @DBName + '.' + @Table_Owner + '.'
                + @TableName + ' because it was processed within the last 24 hours.'
	   PRINT @strTmp
  END
END
END CALLOUT B

-- Close and deallocate the cursor
CLOSE TableList
DEALLOCATE TableList

DROP TABLE #FragList
GO

uspBuildFragList 'northwind'