sqlserver重建所有索引的存储过程

最新推荐文章于 2023-09-25 14:42:51 发布

转载最新推荐文章于 2023-09-25 14:42:51 发布 · 886 阅读

SQL Server 专栏收录该内容

31 篇文章

订阅专栏

本文介绍了一个SQL Server存储过程，用于自动重建碎片化的索引。该过程通过查询系统目录视图和动态管理视图来识别需要重建的索引，并执行相应的ALTER INDEX命令。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE proc [dbo].[reBuildIndex]
as
declare @statement NVARCHAR(1000)
declare mycursor cursor for
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )'
      as sqlStr
FROM   sys.indexes AS ix
       INNER JOIN sys.tables t
       ON     t.object_id = ix.object_id
       INNER JOIN sys.schemas s
       ON     t.schema_id = s.schema_id
       INNER JOIN
              (SELECT object_id                   ,
                      index_id                    ,
                      avg_fragmentation_in_percent,
                      partition_number
              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
              ) ps
       ON     t.object_id = ps.object_id
          AND ix.index_id = ps.index_id
       INNER JOIN
              (SELECT  object_id,
                       index_id ,
                       COUNT(DISTINCT partition_number) AS partition_count
              FROM     sys.partitions
              GROUP BY object_id,
                       index_id
              ) pc
       ON     t.object_id              = pc.object_id
          AND ix.index_id              = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 0
   AND ix.name IS NOT NULL
open mycursor

fetch next from mycursor  into @statement
while(@@fetch_status=0)     --如果数据集里一直有数据
begin
EXEC sp_executesql  @statement
--select @statement
fetch next from mycursor  into @statement
end
close mycursor
deallocate mycursor