重建索引存储过程

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Proc_ReBuildIndex') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].Proc_ReBuildIndex
GO
-- =============================================
-- Author:		yng
-- Create date:          2014-09-18
-- Description:	重建索引
-- =============================================
CREATE PROC [dbo].[Proc_ReBuildIndex] (
	@tableNames NVARCHAR(MAX) =NULL,	--表名(多个以逗号隔开)，默认整理全库的表的索引，时间较长慎用
	@rate INT =5						---索引碎片在多少以上需要整理。默认值：5%
)
AS
BEGIN
	SET NOCOUNT ON
	--1. 分割表，得到表变量
    DECLARE @table TABLE ( rowNum INT IDENTITY(1,1), tableName NVARCHAR(500))
	
	IF ISNULL(@tableNames,'')=''
		BEGIN
			INSERT INTO @table (tableName) 
			SELECT [NAME] FROM SYS.tables t WHERE TYPE='U'
		END
	ELSE
		BEGIN
			SET @tableNames = REPLACE(REPLACE(REPLACE(REPLACE(@tableNames,char(10),''),CHAR(13),''),CHAR(9),''),' ','')
			INSERT @table (tableName)
				SELECT B.tableName FROM (
						SELECT [value] = CONVERT(XML,'<v>' + REPLACE(@tableNames, ',', '</v><v>')+ '</v>')
				   ) A
				   OUTER APPLY(
						SELECT tableName = N.v.value('.', 'nvarchar(max)') FROM A.[value].nodes('/v') N(v)
				   ) B
				WHERE  ISNULL(B.tableName, '') != ''     
		END 
	--获得索引列
	declare @myindexs table(id int identity(1,1),tablename nvarchar(128),indexname nvarchar(128),index_id int,type_desc nvarchar(128))

	insert into @myindexs(tablename,indexname,index_id,type_desc)
	select OBJECT_NAME(a.object_id) tablename,a.name indexname,a.index_id,a.type_desc
	from sys.indexes a
	inner join @table  b on a.object_id=OBJECT_ID(tableName)
	where a.type_desc<>'HEAP' and a.is_disabled=0
	order by a.object_id,a.index_id


	DECLARE @tablename1 nVARCHAR(128),@indexname nvarchar(128),@typedesc nvarchar(128),@index_id int,@avg_fragmentation_in_percent float,@sqlstr nvarchar(max)
	CREATE TABLE #temp_table (id INT IDENTITY(1,1) NOT NULL,tableNAME VARCHAR(256),indexname VARCHAR(512),type_Desc VARCHAR(32),avg_fragmenntation_in_percent FLOAT)
	

	--2. 遍历所有的表名, 遍历每个表的每个索引，如果超过@rate ，则构建动态SQL 来重建索引
	DECLARE @i INT,@iMax INT,@page_num INT, @beginT DATETIME
	SET @beginT = GETDATE()
	PRINT CONVERT(VARCHAR(20), GETDATE(), 120) + ' 开始……'

	select @i=1,@imax=isnull(max(id),0) from @myindexs
	while @i<=@imax
	BEGIN
		SELECT @tablename1=tableName,@indexname=indexname,@index_id=index_id,@typedesc=type_desc FROM @myindexs WHERE id=@i
		

		IF OBJECT_ID(@tablename1) IS  NULL   --判断表是否存在，
		 begin 
	 		PRINT '没有找到该表：'+@tablename1+' 请检查是否输入有误!'
	 		GOTO LoopEnd
		 END
		 
		 ---获得表的行数
		 SELECT @page_num=max(dpages)  FROM sysindexes WHERE id=OBJECT_ID(@tableName1)
		 
		 IF (@page_num<100)     --表的页数小于叶不进行索引重建
		 BEGIN
		 	PRINT '表：'+@tableName1+'的页数小于 100，不进行索引重建'
		 	GOTO LoopEnd
		 END 

		 --获得碎片率
		SELECT @avg_fragmentation_in_percent=c.avg_fragmentation_in_percent
		FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(@tablename1),@index_id,null,null) c 
		where c.alloc_unit_type_desc='IN_ROW_DATA'


		IF (@avg_fragmentation_in_percent>=@rate)      --判断是否大于规定的碎片率，是:重建索引，否则不处理
 		BEGIN
 			--PRINT '执行'
 			SET @sqlstr='ALTER INDEX ['+@indexname+'] ON ['+@tablename1+'] REBUILD '
 			--PRINT @sqlstr
 			begin TRY
 				EXEC(@sqlstr);
 				PRINT '执行语句：'+@sqlstr+' 成功'
 			END TRY
 			BEGIN CATCH
				DECLARE @ErrorMessage NVARCHAR(4000)
				DECLARE @ErrorSeverity INT
				DECLARE @ErrorState INT

				SELECT @ErrorMessage = ERROR_MESSAGE(),
						@ErrorSeverity = ERROR_SEVERITY(),
						@ErrorState = ERROR_STATE()
			   
				RAISERROR (@ErrorMessage, 
							@ErrorSeverity, 
							@ErrorState 
							)
			END CATCH
 		END
 		ELSE
 		BEGIN
 			PRINT '表：'+@tableName1+'的索引：'+@indexname+'碎片率小于' + Convert(varchar(5), @rate) + '%，不进行索引重建'
 		END


		LoopEnd:
		PRINT CONVERT(VARCHAR(20), GETDATE(), 120) + ' 完成度: ' + Convert(VARCHAR(5), @i) + '/' +  Convert(VARCHAR(5), @iMax)
			+ ' 当前表: ' + @tableName1 
		--取下一条(表数据)
		SET @i=@i+1

	END

	PRINT CONVERT(VARCHAR(20), GETDATE(), 120) + ' 结束。总耗时：'+ Convert(varchar(20), datediff(ss, @beginT, GETDATE()))+' 秒'
	SET NOCOUNT OFF
END
GO

EXEC sys.sp_addextendedproperty 
	@name=N'Version', @value=N'1.3' , 
	@level0type=N'SCHEMA',@level0name=N'dbo', 
	@level1type=N'PROCEDURE',@level1name=N'Proc_ReBuildIndex'