索引的碎片整理SQL语句

原文出处：https://blog.csdn.net/iteye_2210/article/details/81622929
 

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