Rebulit index for all table in all SQL server 2005 database

Posted on 2013-01-21
Last Modified: 2013-03-06
Dear all,

Any script for me to rebuilt index for all index of all table in all database?

I want to do it all in one file.

I am using SQL server 2005.

Question by:marrowyung
  • 3
  • 2
  • 2
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 100 total points
ID: 38800442
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

should help its what i use

Author Comment

ID: 38800452
The following script should be run without any problem (e.g. : accidently delete/modify any data)

Any one script can do the job for all user database ?

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(, @schemaname = QUOTENAME(
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
              SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
                    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
                            EXEC (@command);
        --PRINT N'Executed: ' + @command;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
LVL 31

Expert Comment

by:James Murrell
ID: 38800455
The one i posted
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 75

Accepted Solution

Anthony Perkins earned 400 total points
ID: 38801890
Any script for me to rebuilt index for all index of all table in all database?
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

I am confused you ask for code to rebuild all tables in all databases and then post a script that only does it for tables that are fragmented by a certain amount.

So if you want the first go with the first solution posted by cs97jjm3, except that it needs to be included in another undocumented function sp_msforeachdb to do all databases.

If on the other hand you want to only do those tables that have fragmented indexes then download and install the scripts from here:

And then your script becomes as simple as the first example:
EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Open in new window


Author Comment

ID: 38813235

"EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30 "

Yeah, that one is listed as the example. It works for you, right?

Author Comment

ID: 38956917
it seems that running this:

use <userdatabase>

EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

the avg fragmentation_in_precent still the same, checking by this:
SELECT DB_NAME(a.database_id) as [Database Name]
            ,OBJECT_NAME( as [Table Name]
            ,a.object_id as [Table Object ID]
            , as [Index Name]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b
      ON a.object_id = b.object_id
      JOIN sys.sysobjects AS SO
      on b.object_id =
      AND a.index_id = b.index_id
where a.database_id = db_id() and SO.xtype = 'U'
order by a.avg_fragmentation_in_percent DESC

any reason ?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38960265

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now