Reindex entire database in SQL Server

DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS DatabaseTable
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('''+ @DatabaseTable + ''')'
PRINT (@sql)
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Advertisement