Within a single database:
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
Or for a full rebuild across multiple databases.
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY
FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN (
'master'
,'msdb'
,'tempdb'
,'model'
,'distribution'
,'rdsadmin'
) -- databases to exclude
--WHERE name IN ('DB1', 'DB2') -- use this to select specific databases and comment out line above
AND STATE = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT
FROM DatabaseCursor
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT
FROM TableCursor
INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' -- with (sort_in_tempdb = on, fillfactor = 90)
RAISERROR (
@cmd
,10
,1
) -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT
FROM TableCursor
INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT
FROM DatabaseCursor
INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursorWas this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article