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'
重建索引存储过程
最新推荐文章于 2024-07-16 06:54:09 发布