Post reply

Index rebuild

  • August 7, 2008 at 11:46 am

    #192150

    Is there a index rebuild script i could use to rebuild all of my production database indexes OR generate one dynamic from my DB? can somebody please help on this?

    Thanks,

  • August 7, 2008 at 12:30 pm

    #854687

    Here is a script to reindex all user tables in a SQL Server (all databases)

    ----------------------------------------------------------------------------------------------

    declare @dbid int

    declare @reindexdbcommand varchar(128)

    declare @ServerDBs table(dbid smallint, reindexdbcommand varchar(128))

    insert into @ServerDBs (dbid, reindexdbcommand)

    select dbid, 'use ' + [name] + '; EXEC sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')";'

    from master.dbo.sysdatabases

    where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')

    while (select count(*) from @ServerDBs)>0

    BEGIN

    set @dbid = (select TOP 1 dbid from @ServerDBs)

    set @reindexdbcommand = (select TOP 1 reindexdbcommand from @ServerDBs where dbid = @dbid)

    exec sp_sqlexec @reindexdbcommand

    delete from @ServerDBs where dbid = @dbid

    END

    ------------------------------------------------------------------------------------------------

    Change the script as per your requirement(i.e. for one db...)

  • August 7, 2008 at 1:30 pm

    #854727

    Thanks for your reply.

    I am looking for something like this: ALTER INDEX ALL ON Customer REBUILD WITH(ONLINE = ON). Is there a way i can generate Alter index for all of my production X database? If yes, how?

  • August 7, 2008 at 2:28 pm

    #854759

    I have a stored procedure that can help you with this.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Ola Hlalengren

    http://ola.hallengren.com

  • August 7, 2008 at 4:21 pm

    #854823

    Thank you Ola.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply