Rebuild indexes in all tables in Sql Server for a set of databases

Here is a convenient Sql script to rebuild all indexes in a set of databases in Sql Server.

RebuildAllDatabaseIndexesInSetOfDatabases.sql

/*
This script loops through all databases with names containing a specified prefix or matching a specific name.
For each database, it rebuilds indexes on all tables to improve performance by reducing fragmentation.

Variables:
- @Prefix: The prefix to match database names.
- @SomeSpecialSharedDatabaseName: The specific database specific shared database name to include in the loop.
- @DatabaseName: The name of the current database in the loop.
- @TableName: The name of the current table in the loop.
- @SQL: The dynamic SQL statement to execute.

Steps:
1. Declare the necessary variables.
2. Create a cursor to loop through the databases.
3. For each database, use another cursor to loop through all tables and rebuild their indexes.
4. Print progress messages for each database and table.
*/

DECLARE @SomeAcmeUnitDatabaseNamePrefix NVARCHAR(255) = 'SomeAcmeUnit';
DECLARE @SomeSpecialSharedDatabaseName NVARCHAR(255) = 'SomeAcmeShared';
DECLARE @DatabaseName NVARCHAR(255);
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DatabaseCount INT;
DECLARE @CurrentDatabaseCount INT = 0;
DECLARE @TableCount INT;
DECLARE @CurrentTableCount INT = 0;
DECLARE @StartTime DATETIME2;
DECLARE @EndTime DATETIME2;
DECLARE @ElapsedTime NVARCHAR(100);

SET @StartTime = SYSDATETIME();

-- Get the total number of databases to process
SELECT @DatabaseCount = COUNT(*)
FROM sys.databases
WHERE [name] LIKE @SomeAcmeUnitDatabaseNamePrefix + '%' OR [name] = @SomeSpecialSharedDatabaseName;

DECLARE DatabaseCursor CURSOR FOR
SELECT [name]
FROM sys.databases
WHERE [name] LIKE @SomeAcmeUnitDatabaseNamePrefix + '%' OR [name] = @SomeSpecialSharedDatabaseName;

OPEN DatabaseCursor;
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentDatabaseCount = @CurrentDatabaseCount + 1;
    SET @SQL = '
	RAISERROR(''*****************************************************************'', 0, 1) WITH NOWAIT;
	RAISERROR(''REBUILDING ALL INDEXES OF TABLES INSIDE DB: %s'', 0, 1, @DatabaseName) WITH NOWAIT;
	RAISERROR(''*****************************************************************'', 0, 1) WITH NOWAIT;

    DECLARE @TableName NVARCHAR(255);
    DECLARE @SQL NVARCHAR(MAX);

    -- Get the total number of tables to process in the current database
    SELECT @TableCount = COUNT(*)
    FROM sys.tables;

    DECLARE TableCursor CURSOR FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + ''.'' + QUOTENAME(name)
    FROM sys.tables ORDER BY QUOTENAME(name) ASC;

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @CurrentTableCount = @CurrentTableCount + 1;
        PRINT ''Rebuilding database indexes on table: '' + @TableName +  ''... (DB: '' + CAST(@CurrentDatabaseCount AS NVARCHAR) + ''/'' + CAST(@DatabaseCount AS NVARCHAR) + '')''  +  ''... (Table: '' + CAST(@CurrentTableCount AS NVARCHAR) + ''/'' + CAST(@TableCount AS NVARCHAR) + '')'';
		--RAISERROR(''..Indexing (hit Ctrl+End to go to latest message inside this buffer)'',0,1) WITH NOWAIT;
        SET @SQL = ''ALTER INDEX ALL ON '' + @TableName + '' REBUILD'';
        EXEC sp_executesql @SQL;
        FETCH NEXT FROM TableCursor INTO @TableName;
        --PRINT ''Rebuilt database indexes on table: '' + @TableName + ''.'';
    END;

    CLOSE TableCursor;
    DEALLOCATE TableCursor;';

    EXEC sp_executesql @SQL,
		N'@CurrentDatabaseCount INT, @DatabaseCount INT, @TableCount INT, @CurrentTableCount INT, @DatabaseName NVARCHAR(255)',
		@CurrentDatabaseCount, @DatabaseCount, @TableCount, @CurrentTableCount, @DatabaseName;

    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;
END;

SET @EndTime = SYSDATETIME()

-- Calculate the elapsed time
DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, @EndTime);
DECLARE @ElapsedMilliseconds INT = DATEPART(MILLISECOND, @EndTime) - DATEPART(MILLISECOND, @StartTime);

-- Combine seconds and milliseconds
SET @ElapsedTime = CAST(@ElapsedSeconds AS NVARCHAR) + '.' + RIGHT('000' + CAST(@ElapsedMilliseconds AS NVARCHAR), 3);

-- Print the elapsed time using RAISERROR
RAISERROR('Total execution time: %s seconds', 0, 1, @ElapsedTime) WITH NOWAIT;


CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

The SQL Script uses loops defined by T-SQL database cursors and uses the sys.databases and sys.tables system views to loop through all the databases matching the set prefixes of 'unit databases' like 'SomeAcmeUnit_0', 'SomeAcmeUnit_1' and 'shared database like 'SomeAcmeShared'. This matches a setup of many databases used in companies, where you just want to rebuild all the indexes in all the tables in all the set of databases. It can be handy to be sure that all indexes are rebuilt.

Enumerating concurrent collections with snapshots in C#

In standard collections in C#, it is not allowed to alter collections you iterate upon using foreach for example, since it throws InvalidOperationException - Collection was modified; enumeration operation may not execute. Concurrent collections can be altered while being iterated. This is the default behavior, allow concurrent behavior while iterating - as locking the entire concurrent collection is costly. You can however enforce a consistent way of iterating the concurrent collection by making a snapshot of it. For concurrent dictionaries, we use the ToArray method.

	var capitals = new ConcurrentDictionary<string, string>{
		["Norway"] = "Oslo",
		["Denmark"] = "Copenhagen",
		["Sweden"] = "Stockholm",
		["Faroe Islands"] = "Torshamn",
		["Finland"] = "Helsinki",
		["Iceland"] = "Reykjavik"
	};

	//make a snapshot of the concurrent dictionary first 
	
	var capitalsSnapshot = capitals.ToArray();
	
	//do some modifications
	
	foreach (var capital in capitals){
		capitals[capital.Key] = capital.Value.ToUpper();
	}

	foreach (var capital in capitalsSnapshot)
	{
		Console.WriteLine($"The capital in {capital.Key} is {capital.Value}");
	}

This outputs:

The capital in Denmark is Copenhagen
The capital in Sweden is Stockholm
The capital in Faroe Islands is Torshamn
The capital in Norway is Oslo
The capital in Finland is Helsinki
The capital in Iceland is Reykjavik  

The snapshot of the concurrent collection was not modified by the modifications done. Let's look at the concurrent collection again and iterate upon it.

	foreach (var capital in capitals)
	{
		Console.WriteLine($"The capital in {capital.Key} is {capital.Value}");
	}

This outputs:

Enumerate capitals in concurrent array - just enumerating with ToArray() - elements can be changed while enumerating. Faster, but more unpredictable
The capital in Denmark is COPENHAGEN
The capital in Sweden is STOCKHOLM
The capital in Faroe Islands is TORSHAMN
The capital in Norway is OSLO
The capital in Finland is HELSINKI
The capital in Iceland is REYKJAVIK

As we can see, the concurrent dictionary has modified its contents and this shows that we can get modifications upon iterating collections. If you do want to get consistent results, using a snapshot should be desired. But note that this will lock the entire collection and involve costly operations of copying the contents. If you do do concurrent collection snapshots, keep the number of snapshots to a minimum and iterate upon these snapshots, preferable only doing one snapshot in one single place in the method for the specific concurrent dictionary.