Daniel's script appears to be a good all encompassing solution, but even he admitted that his laptop ran out of memory. Here is an option I came up with. I based my procedure off of Mohammad Nizamuddin's post on TechNet. I added an initial cursor loop that pulls all the database names into a temporary table and then uses that to pull all the base table names from each of those databases.
You can optionally pass the fill factor you would prefer and specify a target database if you do not want to re-index all databases.
--===============================================================
-- Name: sp_RebuildAllIndexes
-- Arguements: [Fill Factor], [Target Database name]
-- Purpose: Loop through all the databases on a server and
-- compile a list of all the table within them.
-- This list is then used to rebuild indexes for
-- all the tables in all the database. Optionally,
-- you may pass a specific database name if you only
-- want to reindex that target database.
--================================================================
CREATE PROCEDURE sp_RebuildAllIndexes(
@FillFactor INT = 90,
@TargetDatabase NVARCHAR(100) = NULL)
AS
BEGIN
DECLARE @TablesToReIndex TABLE (
TableName VARCHAR(200)
);
DECLARE @DbName VARCHAR(50);
DECLARE @TableSelect VARCHAR(MAX);
DECLARE @DatabasesToIndex CURSOR;
IF ISNULL( @TargetDatabase, '' ) = ''
SET @DatabasesToIndex = CURSOR
FOR SELECT NAME
FROM master..sysdatabases
ELSE
SET @DatabasesToIndex = CURSOR
FOR SELECT NAME
FROM master..sysdatabases
WHERE NAME = @TargetDatabase
OPEN DatabasesToIndex
FETCH NEXT FROM DatabasesToIndex INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TableSelect = 'INSERT INTO @TablesToReIndex SELECT CONCAT(TABLE_CATALOG, ''.'', TABLE_SCHEMA, ''.'', TABLE_NAME) AS TableName FROM '
+ @DbName
+ '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''base table''';
EXEC sp_executesql
@TableSelect;
FETCH NEXT FROM DatabasesToIndex INTO @DbName
END
CLOSE DatabasesToIndex
DEALLOCATE DatabasesToIndex
DECLARE @TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM @TablesToReIndex
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName, ' ', @FillFactor)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END