Run a SQL query on multiple databases on a single server, single instance

Question

I know there are a number of posts regarding the topic but I would like you to provide a solution to run the following (rebuild indexes) query on multiple databases, each with the same structure and hosted in the same instance.

The query is:

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

I can run this manually and it works but there are 180 databases on this instance.

Why reinvent the wheel? Assuming you're using SQL Server sql-server-index-and-statistics-maintenance — Stu
– Stu, Commented Mar 21, 2023 at 8:36

Learning · Accepted Answer · 2023-03-21 08:00:37Z

0

This code will rebuild all indexes on all tables for every user database, except for system databases like master, tempdb, model, and msdb. You can modify the WHERE clause in the cursor to exclude additional databases if needed.

Make sure to take database backup make or to test this on a non-production environment first to ensure it does not cause any unexpected issues.

DECLARE @dbname NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = 'USE ' + QUOTENAME(@dbname) + '; ALTER INDEX ALL ON ALL TABLES REBUILD;'
  EXEC sp_executesql @sql
  FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

answered Mar 21, 2023 at 8:00

Learning

20.1k44 gold badges193 silver badges400 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run a SQL query on multiple databases on a single server, single instance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related