2023-07-23 21:31阅读: 460评论: 0推荐: 0

用于重建所有表和所有数据库的所有索引的 SQL Server 脚本

问题

DBA 的主要职责之一是维护索引。关于用于索引重建和索引重组的不同命令以及使用 SQL Server 进行索引维护之间的差异，已经写了一些技巧。此外，还编写了有关使用维护计划来维护所有数据库上的索引的其他技巧。维护计划的问题之一是它们似乎并不总是像您希望的那样可靠，并且有时您还会收到关于任务是否实际成功的错误反馈。在本技巧中，我们将介绍一个可用于重建所有数据库的所有索引的简单脚本。

解决方案

维护计划的一个好处是它们可以跨多个数据库工作，因此您可以推出一项任务来处理所有数据库中的相同活动。我发现维护计划的问题是有时它们不能按预期工作，因此在本技巧中我提供了另一种方法。

下面的脚本允许您为所有数据库和数据库中的所有表重建索引。可以进一步调整以仅处理需要基于碎片级别维护的索引，然后进行索引重组或索引重建。

该脚本使用两个游标，一个用于数据库，另一个用于数据库内的表。

为 SQL Server 2005 及更高版本重建所有索引脚本

以下脚本适用于 SQL Server 2005 及更高版本。

因为我们需要从一个数据库切换到另一个数据库，所以我们需要为查询创建动态 SQL 代码。该代码使用 master.sys.databases 来获取数据库列表并检查数据库的状态，以确保我们正在使用在线数据库。这还使用 INFORMATION_SCHEMA.TABLES 来获取数据库中的用户表列表。

DECLARE @Database NVARCHAR(255)   
DECLARE @Table NVARCHAR(255)  
DECLARE @cmd NVARCHAR(1000)  
 
DECLARE DatabaseCursor CURSOR READ_ONLY FOR 
SELECT name FROM master.sys.databases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- databases to exclude
--WHERE name IN ('DB1', 'DB2') -- use this to select specific databases and comment out line above
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1  
 
OPEN DatabaseCursor  
 
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0  
BEGIN 
 
   SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +  
   table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  
 
   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   
 
   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0   
   BEGIN
      BEGIN TRY   
         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
         --PRINT @cmd -- uncomment if you want to see commands
         EXEC (@cmd) 
      END TRY
      BEGIN CATCH
         PRINT '---'
         PRINT @cmd
         PRINT ERROR_MESSAGE() 
         PRINT '---'
      END CATCH
 
      FETCH NEXT FROM TableCursor INTO @Table  
   END  
 
   CLOSE TableCursor   
   DEALLOCATE TableCursor  
 
   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor