Reindex All Tables in a Database

Reindex All Tables in a Database

DECLARE @dbName SYSNAME

SET @dbName = DB_NAME()

DECLARE @objName sysname
DECLARE @objSchema sysname
DECLARE @idxName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT
CREATE TABLE ##Indexes
(
Indexes_ID INT IDENTITY(1, 1) NOT NULL,
IndexName sysname NOT NULL,
SchemaName sysname NOT NULL,
ObjectName sysname NOT NULL,
STATUS INT NOT NULL
)
-- non-unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, SchemaName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], u.[name], o.[name], i.status '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.[id] = o.[id] '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysusers u '
SET @SQL = @SQL + 'ON o.[uid] = u.[uid] '
SET @SQL = @SQL + 'WHERE i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) = 0'
EXEC sp_executesql @statement = @SQL
-- non-clustered indexes except for those tables that have clustered non-unique indexes; these will be rebuilt automatically
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, SchemaName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], u.[name], o.[name], i.status '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.[id] = o.[id] '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysusers u '
SET @SQL = @SQL + 'ON o.[uid] = u.[uid] '
SET @SQL = @SQL + 'WHERE i.indid > 1 AND i.indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) >= 0 AND '
SET @SQL = @SQL + 'o.[name] NOT IN (SELECT ObjectName FROM ##Indexes)'
EXEC sp_executesql @statement = @SQL
-- unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, SchemaName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], u.[name], o.[name], i.status '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.[id] = o.[id] '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysusers u '
SET @SQL = @SQL + 'ON o.[uid] = u.[uid] '
SET @SQL = @SQL + 'WHERE i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) <> 0'
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName, @objSchema = SchemaName
FROM ##Indexes
ORDER BY Indexes_ID
SET @RowCnt = @@ROWCOUNT
WHILE @RowCnt <> 0
BEGIN
SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.' + @objSchema + '.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'
-- PRINT @SQL
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName, @objSchema = SchemaName
FROM ##Indexes
WHERE Indexes_ID > @ID
ORDER BY Indexes_ID
SET @RowCnt = @@ROWCOUNT
END
DROP TABLE ##Indexes