When working with MSSQL Server 2008 databases containing hundreds of tables, manual index maintenance becomes impractical. The standard ALTER INDEX ALL ON TABLENAME REBUILD command requires individual execution for each table, which isn't feasible for databases with extensive schemas.

We can leverage system views to generate and execute dynamic SQL statements that rebuild all indexes across all tables:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    'ALTER INDEX ALL ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] REBUILD;' + CHAR(13)
FROM 
    information_schema.tables 
WHERE 
    TABLE_TYPE = 'BASE TABLE';

-- Print the generated SQL for verification
PRINT @sql;

-- Uncomment to execute
-- EXEC sp_executesql @sql;

For better control and monitoring, here's an improved script that includes:

• Execution time tracking
• Progress output
• Error handling

DECLARE @tableName NVARCHAR(256)
DECLARE @schemaName NVARCHAR(256)
DECLARE @fullName NVARCHAR(512)
DECLARE @sql NVARCHAR(MAX)
DECLARE @startTime DATETIME
DECLARE @counter INT = 0
DECLARE @totalTables INT

-- Get total table count
SELECT @totalTables = COUNT(*) 
FROM information_schema.tables 
WHERE TABLE_TYPE = 'BASE TABLE'

PRINT 'Starting index rebuild on ' + CAST(@totalTables AS VARCHAR(10)) + ' tables...'

DECLARE table_cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @schemaName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @counter = @counter + 1
    SET @fullName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
    SET @sql = 'ALTER INDEX ALL ON ' + @fullName + ' REBUILD'
    
    PRINT 'Processing (' + CAST(@counter AS VARCHAR(10)) + '/' + 
          CAST(@totalTables AS VARCHAR(10)) + '): ' + @fullName
    SET @startTime = GETDATE()
    
    BEGIN TRY
        EXEC sp_executesql @sql
        PRINT '  Completed in ' + 
              CAST(DATEDIFF(SECOND, @startTime, GETDATE()) AS VARCHAR(10)) + ' seconds'
    END TRY
    BEGIN CATCH
        PRINT '  ERROR: ' + ERROR_MESSAGE()
    END CATCH
    
    FETCH NEXT FROM table_cursor INTO @schemaName, @tableName
END

CLOSE table_cursor
DEALLOCATE table_cursor

PRINT 'Index rebuild process completed'

For SQL Server Enterprise Edition, consider using online index rebuilds to minimize downtime:

-- Generate online rebuild commands (Enterprise Edition only)
DECLARE @onlineRebuildSQL NVARCHAR(MAX) = ''

SELECT @onlineRebuildSQL = @onlineRebuildSQL + 
    'ALTER INDEX ALL ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] REBUILD WITH (ONLINE = ON);' + CHAR(13)
FROM 
    information_schema.tables 
WHERE 
    TABLE_TYPE = 'BASE TABLE';

PRINT @onlineRebuildSQL;
-- EXEC sp_executesql @onlineRebuildSQL;

• Schedule this operation during maintenance windows as it locks tables
• Monitor transaction log growth during execution
• Consider breaking large databases into batches
• For very large databases, evaluate using the Ola Hallengren maintenance solution
• Test scripts in non-production environments first

When working with large MSSQL databases containing hundreds of tables, manually rebuilding indexes becomes impractical. The standard ALTER INDEX ALL ON TABLENAME REBUILD syntax requires individual table names, which doesn't scale for enterprise databases.

We can leverage system views to generate dynamic SQL that rebuilds all indexes across all tables:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    'ALTER INDEX ALL ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] REBUILD;' + CHAR(13)
FROM information_schema.tables 
WHERE TABLE_TYPE = 'BASE TABLE';

EXEC sp_executesql @sql;

For better monitoring during execution, add print statements:

DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @tableCount INT = 0;

SELECT @tableCount = COUNT(*)
FROM information_schema.tables 
WHERE TABLE_TYPE = 'BASE TABLE';

SELECT @sql = @sql + 
    'PRINT ''Rebuilding indexes on: [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'';' + CHAR(13) +
    'ALTER INDEX ALL ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] REBUILD;' + CHAR(13) +
    'PRINT ''Completed: ' + CAST(ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA, TABLE_NAME) AS VARCHAR) + 
    ' of ' + CAST(@tableCount AS VARCHAR) + ''';' + CHAR(13) + CHAR(13)
FROM information_schema.tables 
WHERE TABLE_TYPE = 'BASE TABLE';

EXEC sp_executesql @sql;

For more targeted maintenance, first analyze fragmentation levels:

SELECT 
    OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName,
    indexstats.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN 
    sys.indexes ind ON ind.object_id = indexstats.object_id
WHERE 
    indexstats.avg_fragmentation_in_percent > 30 -- Only heavily fragmented indexes
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC;

Create a stored procedure for scheduled jobs:

CREATE PROCEDURE dbo.RebuildAllIndexes
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @sql NVARCHAR(MAX) = '';
    DECLARE @startTime DATETIME = GETDATE();
    
    PRINT 'Index rebuild process started at: ' + CONVERT(VARCHAR, @startTime, 120);
    
    SELECT @sql = @sql + 
        'PRINT ''Rebuilding indexes on: [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'';' + CHAR(13) +
        'ALTER INDEX ALL ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] REBUILD;' + CHAR(13)
    FROM information_schema.tables 
    WHERE TABLE_TYPE = 'BASE TABLE';
    
    EXEC sp_executesql @sql;
    
    PRINT 'Index rebuild process completed in: ' + 
          CAST(DATEDIFF(SECOND, @startTime, GETDATE()) AS VARCHAR) + ' seconds';
END

• Run during maintenance windows - this operation locks tables
• Consider using ONLINE rebuilds for Enterprise Edition (SQL Server 2012+)
• Monitor transaction log size during execution
• For very large databases, batch the operations