索引的碎片整理SQL语句

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

--设置最大允许的碎片数量,超过则对索引进行碎片整理
SET @maxfrag = 30.0

CREATE TABLE #fraglist (
   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)

--获取当前数据库中所有数据表的索引碎片信息,并保存到临时表
DECLARE tables CURSOR FOR SELECT TABLE_NAME   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_TYPE = 'BASE TABLE'
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 BEGIN
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT FROM tables INTO @tablename
END
CLOSE tables
DEALLOCATE tables

/*
ObjectName:数据表名称
IndexId:索引ID
LogicalFrag:逻辑碎片值
*/
DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag  FROM #fraglist
      WHERE LogicalFrag >= @maxfrag
         AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
OPEN indexes
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0 BEGIN

 
   PRINT '正在执行 DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'

   --执行索引碎片整理
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'

   EXEC (@execstr)

   FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
END
CLOSE indexes
DEALLOCATE indexes

DROP TABLE #fraglist
GO