Agent Jobs

Rebuild All Indexes Across All Databases And All Tables

How to Rebuild all Indexes across all Tables in all Databases with a single job step

Here’s some SQL logic I wrote up to rebuild ALL the indexes across ALL tables. Later I may include some extra code to identify those indexes that are fragmented above 70% to be REBUILD, and those indexes that are below 70% to be REORGANIZE. This script is not set to rebuild and leave tables online. For this you need to add the *WITH (ONLINE = ON); therefore you should run this as general maintenance after hours. It’s just much faster to rebuild directly without using the ONLINE = ON. I’m not against doing it online, but I do advocate for the quickest approach normally. This process will target tables that are reflected as BASE TABLE from information_schema.tables. Additionally; it will not target the system databases so Master, Model, Msdb, and Tempdb are all excluded.

You will notice some replacements going on. This is so the logic responsible for producing the rebuild statement will create the appropriate variables adding the database name as the suffix to ensure uniqueness. Obviously; if the database name that it’s adding to the variable name has any spaces, dots(.), etc. it will break the logical flow so to address this I simply replace the spaces with underscores, and the dots(.) also with underscores. Easy.

On with the logic.

use master;
set nocount on

declare @index_maintenance	varchar(max)
set	@index_maintenance	= ''
select	@index_maintenance	= @index_maintenance +
'use [' + sd.name + '];' + char(10) +
'declare	@rebuild_indexes_in_' + replace(replace(sd.name, ' ', '_'), '.', '_') + '	varchar(max)'	+ char(10) +
'set		@rebuild_indexes_in_' + replace(replace(sd.name, ' ', '_'), '.', '_') + '	= '''''			+ char(10) +
'select	@rebuild_indexes_in_' + replace(replace(sd.name, ' ', '_'), '.', '_') + '	= @rebuild_indexes_in_' + replace(replace(sd.name, ' ', '_'), '.', '_') + ' +
''alter index all on [dbo].['' + object_name(sddips.object_id) + ''] rebuild;'' + char(10)
from sys.dm_db_index_physical_stats (db_id(), null, null, null, null) sddips
join information_schema.TABLES ist on object_name(sddips.object_id) = ist.table_name
where ist.table_type = ''base table''
exec (@rebuild_indexes_in_' + replace(replace(sd.name, ' ', '_'), '.', '_') + ')' + CHAR(10) + CHAR(10)
from sys.databases sd where name not in ('master', 'model', 'msdb', 'tempdb') order by name asc
select	(@index_maintenance) for xml path(''), type
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s