SQL-Ex blog

Решение

Преимуществом планов обслуживания является их способность работать с несколькими базами данных и, следовательно, вы можете выставить одну задачу для выполнения аналогичных действий по всем вашим базам данных. Проблема, с которой я сталкиваюсь при использовании планов обслуживания, состоит в том, что они не всегда работают, как ожидалось. Поэтому я предлагаю здесь другой подход.

Приведенный ниже скрипт позволяет перестроить индексы для всех баз данных и всех таблиц в базе. Его можно упростить для обработки только тех индексов, которые необходимо обслуживать, основываясь на уровне фрагментации, а также выполнения либо реорганизации индекса, либо его перестройки.

Скрипт использует два курсора - один для баз данных, а другой - для таблиц в базе данных.

Скрипт для перестройки всех индексов в версиях 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')  -- исключаемые базы данных

--WHERE name IN ('DB1', 'DB2') -- используется для выбора конкретных баз данных (раскомментируйте эту строку)

AND state = 0 -- база данных онлайн

AND is_in_standby = 0 -- база данных не читается только для 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'''   

   -- создаем курсор для таблиц

   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 -- раскомментируйте, если хотите увидеть команды

         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

Замечания

Я исключил системные базы данных, так что вы можете включить их или добавить другие базы данных для исключения их из процедур обслуживания индексов. Вы можете также изменить код для включения только конкретных баз данных.

Скрипт тестировался и будет работать на SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 и SQL Server 2019.