SQL Soundings: Rebuild All Indexes on All Tables

Thursday, November 13, 2008

Rebuild All Indexes on All Tables

Here's a small script to rebuild all the indexes on all the tables in a given database:

EXEC sp_MsForEachTable 'USE [YourDatabase]  PRINT ''?''  DBCC DBREINDEX (''?'', '' '', 70)'

Be sure to read Books OnLine about "DBCC DBREINDEX" - it's probably not something you want to do during peak hours.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

1204 (1)

add constraint (2)

agent ransack (1)

agent_datetime (1)

aggregates (1)

all_indexes (1)

alter column (1)

alter database (1)

alter index (1)

alter table (4)

alter user (1)

ansi_nulls (2)

ansi_padding (1)

ansi_warnings (1)

arithabort (1)

autogrowth (1)

autoshrink (1)

avg (1)

b-tree (1)

backup (2)

backup_finish_date (1)

backupset (1)

batteries (1)

begin transaction (1)

between (1)

bigint (1)

bit (1)

cardinality (1)

case (3)

cast (4)

catch (1)

Central Management Servers (7)

charindex (2)

checkpoint (1)

clustered index (5)

coalesce (1)

collation (1)

comma-separated (1)

commit (1)

concat_null_yields_null (1)

convert (4)

cpu (1)

create table (1)

create user (1)

database trigger (1)

databaseproperty (1)

datalength (2)

datetime (1)

db_name (1)

dbcc checkdb (2)

dbcc cleantable (2)

dbcc dbreindex (1)

dbcc loginfo (1)

dbcc showcontig (1)

dbcc shrinkfile (1)

dbcc updateusage (2)

default constraint (3)

defragmentation (1)

delete (1)

disable trigger (1)

disk (1)

drop (2)

drop constraint (1)

drop index (1)

drop statistics (2)

drop table (1)

editionid (1)

email (1)

email_address (1)

eml files (1)

enable trigger (1)

error (1)

error_message (1)

errorlog (2)

escape (2)

exception handling (1)

exec (1)

execute as caller (1)

extended properties (1)

extent (1)

extent changes (1)

external fragmentation (2)

fillfactor (1)

floor (2)

fn_trace_gettable (1)

foreign key (1)

forwarding pointer (1)

fragmentation (2)

from (1)

function (1)

grants (1)

group by (2)

group by rollup (1)

grouping (1)

hexadecimal (1)

hobt (1)

identity (1)

implicit conversion (1)

index (6)

indexed views (1)

indexid (1)

indexproperty (1)

information_schema (1)

insert (1)

instant file initialization (1)

int (1)

internal fragmentation (1)

is_auto_shrink_on (1)

is_computed (1)

is_identity (4)

is_ms_shipped (6)

is_nullable (3)

is_primary_key (3)

is_system_named (1)

is_unique_constraint (1)

ishypothetical (1)

isnull (2)

IsReadOnly (1)

isstatistics (1)

isunique (1)

Job (1)

join (1)

ldf (1)

like (1)

linked server (1)

lock (1)

log file (1)

log10 (1)

logical fragmentation (2)

master database (4)

mdf (1)

member_principal_id (1)

money (2)

msg 1750 (1)

msg 1770 (1)

no_infomsgs (1)

nocount (2)

nomenclature (1)

non-clustered index (1)

nonclustered index (1)

nowait (1)

ntfscontrolfile (1)

nullif (1)

nvarchar (2)

nvarchar(max) (1)

object_id (2)

object_name (3)

objectproperty (1)

openquery (1)

operators (2)

order by (1)

order of evaluation (1)

outer join (1)

page splits (1)

partition (1)

permissions (1)

pivot (1)

plural (1)

primary key (2)

principals (1)

profiler (2)

quoted_identifier (2)

quotename (3)

raiserror (1)

rebuild (1)

rebuild. reorganize (1)

recovery model (1)

replace (4)

replacechar (1)

reverse (1)

role_principal_id (1)

roles (1)

rowcount (2)

rtrim (1)

scan density (1)

schema_id (2)

screen saver (2)

selectivity (1)

serverproperty (4)

sid (1)

single quotes (1)

singular (1)

skibear (1)

sp_add_notification (1)

sp_addrolemember (1)

sp_change_users_login (1)

sp_delete_notification (1)

sp_delete_operator (1)

sp_msforeachdb (11)

sp_msforeachtable (2)

sp_readerrorlog (1)

sp_send_dbmail (1)

sp_sqlexec (1)

sp_update_job (1)

sparse (1)

sql_oj_capabilities (1)

sqlcmd (1)

SQLServerPedia Syndication (55)

SSIS (3)

SSMS (5)

statistics (3)

statistics io (1)

stats_date (1)

stuff (1)

substring (2)

suser_sid (1)

sys.allocation_units (1)

sys.columns (7)

sys.data_spaces (1)

sys.database_files (2)

sys.database_role_members (1)

sys.databases (3)

sys.dm_db_index_physical_stats (1)

sys.dm_db_index_usage_stats (2)

sys.dm_db_partition_stats (2)

sys.dm_exec_cached_plans (1)

sys.dm_exec_query_plan (1)

sys.dm_exec_query_stats (1)

sys.dm_exec_requests (2)

sys.dm_exec_sql_text (1)

sys.dm_os_sys_info (1)

sys.extended_properties (2)

sys.index_columns (2)

sys.indexes (9)

sys.key_constraints (2)

sys.master_files (2)

sys.objects (6)

sys.partitions (1)

sys.schemas (1)

sys.sp_addextendedproperty (2)

sys.sp_autostats (1)

sys.sp_createstats (1)

sys.sp_dropextendedproperty (1)

sys.sp_msforeachdb (2)

sys.sp_msforeachtable (1)

sys.sql_modules (1)

sys.stats (2)

sys.stats_columns (1)

sys.sysprocesses (1)

sys.tables (11)

sys.types (3)

sysalerts (1)

syscolumns (1)

syscomments (1)

sysdatabases (3)

sysindexes (3)

sysjobhistory (2)

sysjobs (6)

sysjobs_view (1)

sysjobshistory (1)

syslogins (1)

sysname (2)

sysnotifications (1)

sysobjects (4)

sysoperators (2)

system stored procedure (1)

table (1)

tableresults (1)

tempdb (1)

templates (2)

then (1)

tinyint (1)

tinyurl.com (1)

trace (1)

trancount (1)

transactions (1)

trc (1)

trigger (1)

truncate table (1)

truncateonly (1)

try (1)

type_desc (1)

uid (1)

undocumented (1)

unique constraint (1)

use (1)

user_name (2)

varbinary (1)

views (1)

virtual log file (1)

Visio (1)

vista (1)

vista mail (1)

Visual Studio (1)

when (1)

whitespace (1)

xact_abort (2)

xp_cmdshell (4)

xp_fileexist (2)

xp_fixeddrives (1)

xp_instance_regwrite (1)

xtype (1)