SQL Server script to rebuild all indexes for all tables and all databases

By:   |   Updated: 2022-02-23   |   Comments (85)   |   Related: 1 | 2 | 3 | 4 | 5 | > Fragmentation and Index Maintenance


One of the main functions of a DBA is to maintain indexes. There have been several tips written about different commands to use for index rebuilds and index reorgs as well as the differences between index maintenance with SQL Server. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. In this tip we look at a simple script that could be used to rebuild all indexes for all databases.


The one nice thing about maintenance plans is that they work across multiple databases and therefore you can push out one task to handle the same activity across all of your databases. The problem that I have seen with maintenance plans is that sometimes they do not work as expected, therefore in this tip I provided another approach.

The script below allows you to rebuild indexes for all databases and all tables within a database. This could be further tweaked to handle only indexes that need maintenance based on fragmentation levels as well as then doing either an index reorg or an index rebuild.

The script uses two cursors one for the databases and another for the tables within the database.

Rebuild All Indexes Script for SQL Server 2005 and Later

The below script will work with SQL Server 2005 and later versions.

Because we need to change from database to database we need to create dynamic SQL code for the queries. The code uses master.sys.databases to get a list of databases as well as check the status for the database to make sure we are working with databases that are online. This also uses INFORMATION_SCHEMA.TABLES to get a list of user tables in the database.

DECLARE @Database NVARCHAR(255)   

SELECT name FROM master.sys.databases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- 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

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

   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   
      BEGIN TRY   
         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' 
         --PRINT @cmd -- uncomment if you want to see commands
         EXEC (@cmd) 
      END TRY
         PRINT '---'
         PRINT @cmd
         PRINT '---'

      FETCH NEXT FROM TableCursor INTO @Table   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor


I have excluded the system databases, so you can include these or add other databases to exclude from you index maintenance routines. Also, you can change the code to include only specific databases instead.

This script has been tested and will work with SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 and SQL Server 2019.

Next Steps
  • This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index rebuild or an index defrag.
  • Make the index rebuild statements more robust with other options.
  • You could also modify this to read from a table that you create to identify which databases and which indexes you want to run this against. You can look at index fragmentation and only rebuild the indexes that need to be rebuilt.
  • This approach rebuilds all indexes, so be careful if you run this on very large indexes or large databases because it will take some time to complete and consume memory, cpu and disk resources.
  • Take a look at these other index related tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-02-23

Comments For This Article

Monday, September 5, 2022 - 9:41:27 PM - Raul Back To Top (90441)
Exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'

Tuesday, July 19, 2022 - 11:43:53 AM - Greg Robidoux Back To Top (90286)
Hi Jeff, thanks for the input. I agree this is not the ideal situation, but this comes in very handy when you have lots of small databases that you need to update. There are several other articles on the site that show how to be selective when rebuilding indexes.


Tuesday, July 19, 2022 - 11:32:19 AM - Jeff Moden Back To Top (90283)
While I appreciate anyone that steps up to the plate with an article and a script, be real careful folks. Rebuilding ALL indexes is a form of "Death by SQL".

First, you have a whole lot of indexes that simply don't need any maintenance. Rebuilding those is a total waste of time and resources and will make your log file much more busy that it needs to be, which also affects how long your log file backups take and how big they are.

Second, if you do have indexes that are fragmenting and you don't actually know why, then rebuilding them may 1)) be a total waste of time for "append only but expansively updated" types of indexes and 2) if the FILL FACTOR doesn't actually help OR your FILL FACTOR is still set to 0 (same as 100), then you've just removed ALL freespace from the index and you will suffer from massive page splits on the proverbial "morning after" which makes for a huge log file and a whole lot of blocking (while the pages split), and more.

Wednesday, March 9, 2022 - 12:02:33 PM - Christopher Thumann Back To Top (89861)
You. Are. Awesome.

Wednesday, February 23, 2022 - 2:06:17 PM - Sudham Chintapatala Back To Top (89827)
Excellent script- worked the very first time.

Sunday, November 8, 2020 - 2:19:50 AM - HAMED Back To Top (87771)
Excellent script, thanks you for sharing !

Thursday, October 1, 2020 - 3:03:45 AM - Kapil Nangla Back To Top (86574)
I run this query for rebuild all tables's indexes. But after successful completion most of cluster tables are not rebuild.

Wednesday, September 30, 2020 - 1:26:12 AM - Kapil Nangla Back To Top (86558)
Very useful. Thanks for post sir. Please keep posting these helpful posts.

Monday, March 23, 2020 - 10:28:01 AM - Mircea Back To Top (85170)

Excellent script, thanks you for sharing !

Tuesday, July 23, 2019 - 4:39:56 AM - patrice lavielle Back To Top (81839)

A big thanks for your script.

I don't know much about SQL and after a big headache this script saved my life. Many thanks.

Tuesday, March 15, 2016 - 1:36:16 AM - Matthew Holloway Back To Top (40937)

 Excellent idea Greg,

The following ammendment to the cursor works in my dev environments for 2012.




 FROM sys.databases a

inner join sys.database_mirroring b

on a.database_id = b.database_id

WHERE a.name NOT IN ('master','msdb','tempdb','model','distribution')

And (b.mirroring_role <> 2 or b.mirroring_role is Null and a.state <> 6) 

Friday, March 11, 2016 - 7:41:11 AM - Greg Robidoux Back To Top (40912)

Thanks Matthew for this addition.  The script probably should be updated to make sure the database is online as well as the mirroring check you put in place.  Thanks again for providing feedback, I am sure this will help many other people too.


Thursday, March 10, 2016 - 11:53:03 PM - Matthew Holloway Back To Top (40909)

 Hello Greg,

Very useful but we have a lot of mirrored db's that stop this method dead so I updated your cursor to the below, I hope this helps someone.



SELECT name 

FROM master.dbo.sysdatabases a

inner join sys.database_mirroring b

on a.dbid = b.database_id

WHERE a.name NOT IN ('master','msdb','tempdb','model','distribution')

And (b.mirroring_role <> 2 or b.mirroring_role is Null)





Friday, July 3, 2015 - 2:46:34 AM - Raj Back To Top (38124)

Hi Greg,

Nice article.. thanks for sharing.

But this command do not work for azure sql database.

can u plz share the same for azure sqldb?



Friday, April 10, 2015 - 3:05:05 AM - Nils Back To Top (36887)

Hi Greg,

thank you very much for your quick and helpful reply. I didn't really pay attention to the page_count value before.




Thursday, April 9, 2015 - 11:42:35 AM - Greg Robidoux Back To Top (36878)

Hi Nils,

the items you listed are very small with only a couple of pages.  For small objects like this the fragmentation doesn't always go to zero.  Also, since there are very small you can just ignore these items.  Each page is 8K, so these three items (8 pages) are only taking about 64K of space.


Thursday, April 9, 2015 - 10:16:33 AM - Nils Back To Top (36877)

Thank you for this awesome script, which made some of my databases quicker. I also used it on the ReportServer database but there are still fragmented indexes remaining.

Just a few examples:

Tablename    Indexname    index_type_desc    avg_fragmentation_in_percent    page_count 

SegmentedChunk PK_SegmentedChunk CLUSTERED INDEX 66,6666666666667 3
SegmentedChunk IX_ChunkId_SnapshotDataId NONCLUSTERED INDEX 50 2
SegmentedChunk UNIQ_SnapshotChunkMapping NONCLUSTERED INDEX 66,6666666666667 3

Am I missing something? Do I need to change someting in the script?

Any help would be appreciated.









Tuesday, November 18, 2014 - 5:21:36 PM - Kim Back To Top (35329)

This one is simpler.


EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
EXEC sp_updatestats

Wednesday, November 12, 2014 - 6:02:46 AM - Victor Back To Top (35264)

This is an awesome script for once-off brute force index rebuild.

Thanks for this, Greg. It is exactly what you say it is - and is valuable for what it is.

If you want finesse, schedules, reports etc check out www.OlaHallengren.com - Ola's made a whole suite of scripts available for free - and there are great lessons for developers when you open and read his actual code.


Tuesday, October 21, 2014 - 2:30:30 PM - Greg Robidoux Back To Top (35031)

Hi Alex, I would look at fragmentation levels and rebuild or reorganize indexes based on the fragmentation. 

Check out this tip instead that does selective index maintenance versus all indexes: http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/

Tuesday, October 21, 2014 - 1:42:42 PM - Alex Back To Top (35030)


I did a SHRINK on my Database (I had to do) and it realeases around 55% of Available free space on my Data Base, and I've been reading that "Reorganize or Alter index" could be good after doing a SHRINK, Is it really important to run this script?, Will this script increase my .mdf size again?

Thank you!

Tuesday, September 30, 2014 - 5:53:53 AM - jacouh Back To Top (34771)

Thank you very much for sharing this script. This morning we could no more insert newrecords in the products table. After running this rebuilding and defragmentation script, all work as a charm.

Wednesday, May 7, 2014 - 3:29:39 PM - Zack Back To Top (30653)

I like the script. I have a lot of databases in read only or offline status on my development server though - which caused it to fail. So I modified the script for the database cursor slightly... 


SELECT name FROM master.sys.databases

WHERE name NOT IN ('master','msdb','tempdb','model') AND is_read_only<>1 AND state<>6

Wednesday, February 19, 2014 - 9:12:26 AM - Greg Robidoux Back To Top (29501)

Hi Jay,

Take a look at this tutorial about tuning queries: http://www.mssqltips.com/sqlservertutorial/2250/graphical-query-plan-tutorial/

Also, take a look at these tips to gather information about poor performing queries using Profiler and server side traces: http://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/

Tuesday, February 18, 2014 - 8:59:23 PM - Jay Ganesh Back To Top (29497)

Thanks ,

It Works.

I Need a suggession. i useing sql server 2005 for one small Hospital (free Service organisation).

all table record have a relation with one table. when make query with relate that table , is it affect performance ? (in feature)

can you assit me to how optimize and organize the databse with best performance





Tuesday, December 10, 2013 - 7:43:09 AM - mo Back To Top (27747)

exec sp_MSforeachdb '

IF ''!'' NOT IN (''master'',''model'',''msdb'',''tempdb'')


USE [!]; 


END',@replacechar = '!'

Nice & Easy ;)

Friday, November 22, 2013 - 4:25:50 PM - Greg Robidoux Back To Top (27582)

@Lava - yes this script could be modified, but this doesn't get current fragmentation levels it does every index which is not always the best approach.

Take a look at this tip to see if you could use this or combine the two together:


Friday, November 22, 2013 - 3:50:17 PM - Lava Back To Top (27581)

Hi Greg -

Is it possible to modify your script to add a case statement to where the program either rebuild or reorganize based on fragmentation level? I like your way of writing scripts, it is always simple and easy to follow.




Tuesday, November 12, 2013 - 6:57:19 AM - Parker Back To Top (27464)

Remember, this article is a bit aged. Rebuilding indexes, especially on a recurring schedule, is no longer important, and usually waste of resources. The only reason you would do this is for index fragementation where the entirety of the index pages are sequentially organized on a single rotating drive. With most data in multi-physical-drive raid environments, and most certainly with Solid State Drives, reducing fragmentation will not have any impact since the data will still be read from multiple discontinguous points, no matter how many times you defrag those pages. The take-away is that hardware technology changes have made it where data and indexes no longer need to be continguous. In most cases the default fill factor will suffice, but your hardware should be considered before throwing on excess processing usage that serves no purpose; save your CPU, log files, and network traffic for your backups, ETL, reporting, etc.

Thursday, October 24, 2013 - 10:45:24 AM - Carlos Back To Top (27265)

I am new to sql scripts good job guys, good tips... I will be stoping by every so often to check for new tips

Wednesday, October 23, 2013 - 1:20:43 AM - Iyyankann M Back To Top (27235)

Thanks guys, it really helped.

Monday, August 5, 2013 - 1:13:17 PM - Greg Robidoux Back To Top (26111)

@Pat - thanks your your feedback on this tip.  Yes you are correct the better approach is to only maintain indexes that have fragmentation issues.

This is a good alternative for using Maintenance Plans, which rebuilds all indexes, or for small databases where reindexing all indexes is not very time consuming.

I agree a better approach is to selectively rebuild, but maintaining all indexes is better than doing nothing.


Monday, August 5, 2013 - 12:11:02 PM - Pat Back To Top (26109)

I have to question if you really wish to rebuild every index on every table in every database.

At the very least, I would think that you would want to check the degree of fragmentation and the number of pages that the index consumes before deciding if the index even needs to be rebuilt in the first place.  Otherwise, you my be wasting a lot of unnecessary CPU cycles.


Thursday, May 30, 2013 - 11:20:33 AM - Greg Robidoux Back To Top (25202)

@Ashok Kumar 3 - take a look to see if the index rebuilds are being blocked by other proceses.  This is one reason why it may be taking longer to complete.  Also, it maybe helpful to do one database at a time to see if you can pinpoint which database is causing the issues.


Thursday, May 30, 2013 - 6:02:00 AM - ASHOK KUMAR J Back To Top (25194)

Hi Graig,
Excellent Article

I have one question:

We have a 20 databases, We are used to use maintenance Plan to schedule the Weekly rebuild

index job on Every Saturday night 12pm EST. Last

Two Weeks Weekly Rebuild Index job

Running long over 2days? But acutally it might

run 30 mints.

Which is caused this? How to fix this problem?

Friday, May 10, 2013 - 9:07:35 AM - Greg Robidoux Back To Top (23873)

@Paul - have you run Profiler or a server side trace to find queries that take the longest to run?  You can find these queries and then look at exeuction plans to determine how to improve query performance.

Once you have optimized all of your queries the only other option is to add more or faster hardware such as CPU, memory and disks.

Take a look at these other tips:


Thursday, May 9, 2013 - 8:19:39 PM - Paul Back To Top (23850)

@Greg -  Anything else I can do to speed up SQL besides reindexing?

Thursday, May 9, 2013 - 8:14:31 PM - Paul Back To Top (23849)

Perfect, thanks Greg. I appreciate it, this is a big help...

Thursday, May 9, 2013 - 5:31:23 PM - Greg Robidoux Back To Top (23847)

@Paul - yes you can just paste the code from the above into a query window and run this.

If you have small databases this will run quickly, but for larger databases and indexes it will take some time.

When you rebuild an index it will block other users until the index rebuid is complete, unless you are using the ONLINE option which is only available in the Enterprise edition of SQL Server.

Best practice is to rebulid indexes when there is low to no database usage just to eliminate any blocking or heavy I/O that might occur.

You can also change this to limit to one or more databases. 

Let's say you want to do this for only databases Test1 and Test2 change this code:

SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  


SELECT name FROM master.dbo.sysdatabases  
WHERE name IN ('Test1','Test2')  

Thursday, May 9, 2013 - 4:15:42 PM - Paul Back To Top (23846)

I am very new to SQL but I jsut wanted to verify that I can past that code into a new query window for my SQL 2000 server and execute and it will run? 

Also might be a dumb question (I think the answer is yes) but do I have to make sure all users are out of the SQL application before running?

Wednesday, April 17, 2013 - 9:46:59 AM - Greg Robidoux Back To Top (23398)

@Carl - change this part of the code as follows to do just one database

name FROM master.dbo.sysdatabases  
WHERE name IN ('RESCOM')  

Tuesday, April 16, 2013 - 8:55:50 PM - Carl Back To Top (23389)


Thanks for your prompt reply..

so i will paste this query without editing this?..sorry for the question, i dont know if i should put and edit this query indicating the name of my database(RESCOM)..and if yes i dont know ehere to put it..



Tuesday, April 16, 2013 - 10:23:03 AM - Greg Robidoux Back To Top (23376)

@Carl - this will do this for all indexes for all databases.

Tuesday, April 16, 2013 - 6:40:05 AM - CArl Back To Top (23370)

Can anyone help me?


would i just run this query on my SQL and will it re index all my databases?or i need to modify or change some of it before running this query?

thanks for the help..



Tuesday, April 2, 2013 - 12:33:40 AM - xexex Back To Top (23107)

@Greg Robidoux It work! Thanks.

Wednesday, March 27, 2013 - 2:40:11 PM - Greg Robidoux Back To Top (23038)

@xerex - you can add this code as shown below.  This should work, but I have not tested it.



and add this

  CLOSE TableCursor
  DEALLOCATE TableCursor  

  FETCH NEXT FROM TableCursor INTO @Table

Wednesday, March 27, 2013 - 5:56:43 AM - xexex Back To Top (23032)

@Greg Robidoux - I mean I don't need to hardcore the name to change recovery mode, please see the post in February 25, 2013 and February 26, 2013

Thanks for Reply :)



Wednesday, March 27, 2013 - 5:34:45 AM - Greg Robidoux Back To Top (23031)

@xerex - the names in the following line only exclude databases, otherwise all databases are included:

WHERE name NOT IN ('master','msdb','tempdb','model','distribution')

Wednesday, March 27, 2013 - 2:05:15 AM - xexex Back To Top (23030)

Thanks @Greg Robidoux

How can I add the script so I don't need to hardcore the DatabaseName inside the Database Cursor loop

Wednesday, March 6, 2013 - 5:58:26 PM - brian Back To Top (22611)

 my friend set- up my site and it was hacked. the hacker changed the site completely and moved its location and eased the backups. i rebuilt the site but dont known how to change the server and place the file where it needs to be . any thoughts on how in known its in the pretable but what i am looking greek to me 

Tuesday, February 26, 2013 - 7:35:43 AM - Greg Robidoux Back To Top (22424)

@xexex - take a look at these articles on how to set the recovery model to SIMPLE and FULL




You could add these commands inside the Database Cursor loop.

After you revert back to Full you need to make sure you do a full backup again, becuase the log chain will be broken for restoring your last full backup and your transaction log backups.

Monday, February 25, 2013 - 9:34:44 PM - xexex Back To Top (22415)

Thanks, the script is great.

I ran it on my SQL Server, but my transcation gowth large.

I'd like to change my database to "simple mode" before re-index, how can I add this to the script?

Saturday, February 2, 2013 - 2:33:57 AM - Dhanesh Back To Top (21874)

Hi Greg,

can you plz tell, how to check whether index is rebuidl or not means last index rebuild date

Sunday, January 27, 2013 - 2:09:22 PM - Greg Robidoux Back To Top (21737)

Hi Brent, thanks for the feedback.  I haven't looked at this tip in quite some time.  That's kind of weird I made master upper case.  Oh well, thanks again for the note and this has been fixed.

Thanks again.

Sunday, January 27, 2013 - 11:03:31 AM - Brent Ozar Back To Top (21735)

Hi, Greg. One quick tweak to the script - in the below line, switch MASTER to master.  On case-sensitive instances, it'll matter.  Thanks sir!


SELECT name FROM MASTER.dbo.sysdatabases   

Tuesday, October 23, 2012 - 7:20:35 AM - Thamaraiselvan Back To Top (20049)

request came for the recreation of the indexes of all EVS tables (DAT_EVS_*) in DATxdb_work schema on URANUS-DAT.


Please copy, the existing indexes of EVS tables (DAT_EVS_*) in EARTH-DAT DATXdb TO  URANUS-DAT DATxdb_work EVS tables (DAT_EVS_*)


Please do the same for the primary keys as well.



Thursday, August 23, 2012 - 6:47:25 PM - ilir dokle Back To Top (19203)

This work just great thanx a lot.

Tuesday, May 22, 2012 - 7:50:42 AM - Sherbaz Mohamed C P Back To Top (17585)

With the help of some forums and blogs, I have designed a script that will automatically decide and rebuild or reorg all indexes in a database based on fragmentation level. Please have a look and let me know your inputs.


Saturday, May 19, 2012 - 1:32:18 PM - venkat Back To Top (17551)

i have executed the script given in the begining of this page. it has executed succesfully. can you provide me a script that lets me know how the rebuild operation has performed.




Thursday, May 3, 2012 - 9:56:32 AM - Greg Robidoux Back To Top (17253)

BobC - I agree it is better to only maintain the indexes that need maintenance instead of doing all indexes.  This is a good approach for people that want to do something and not have to put to much thought into the process.  Also, this is a good approach for SQLExpress where you cannot setup maintenance plans.

Here is another tip that does selective index maintenance: http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/

Thursday, May 3, 2012 - 8:16:42 AM - BobC Back To Top (17251)

I second the suggestion to use Ola Hallengren's: http://ola.hallengren.com, or a similar one that will test fragmentation and reogr/rebuild accordingly. 

See also:


Sunday, February 5, 2012 - 2:31:47 AM - Anil Lobo Back To Top (15905)

Thanks guys, it really helped.

Monday, January 9, 2012 - 7:01:06 AM - Ramesh Back To Top (15546)

Generally I will use the rebuild index script for all my production database. It runs quite good and helps me alot.

Recently i have ran this script at my another production server database which has very huge data. The size of MDF is 30GB.

I got the below mentioned error, can you tell me what would be reason for that and how should i rectify.

***** Error **************

Database name 'tempdb' ignored, referencing object in tempdb.

Msg 1088, Level 16, State 9, Line 1

Cannot find the object "#TEMP_Location_Scanner_Counts_______________________________________________________________________________________000000000137" because it does not exist or you do not have permissions.

****************** end of error mesage. *****

Thanks in advance




Sunday, August 28, 2011 - 6:23:36 AM - Greg Robidoux Back To Top (14537)

You can change these lines to use the fill factor that is currenlty in effect for the index:


-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor) 


-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'

-- SQL 2000 command

Sunday, August 28, 2011 - 3:22:23 AM - chandramohan Back To Top (14536)


    When we have indexes with fillfactor other than 90 then this script will rebuild the indexes with new fill factor which is not wanted. So you need to modify the script so that will take the existing fill factors only.


Monday, July 25, 2011 - 3:26:18 PM - Greg Robidoux Back To Top (14261)

Jay - take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1791

Monday, July 25, 2011 - 2:51:26 PM - Jay Back To Top (14260)


The script is good, i am looking for kind of a same script, which show me fragmentation for some tables but not all. how can i do that.

Any help is greatly appreciated.


Tuesday, July 12, 2011 - 11:06:42 AM - Much better alternate Back To Top (14155)

Instead of rebuilding ones that don't need it, try a more comprehensive solution, like

Ola Hallengren's: http://ola.hallengren.com


SQLFool's: http://sqlfool.com/2011/06/index-defrag-script-v4-1/


Friday, June 24, 2011 - 10:22:19 AM - Rickster Back To Top (14075)

Greg ... error was produced using Sql Server 2000 QA tool. I found at least one other Google hit on that error msg from someone else who had posted exact same code.

Thanks Marcio.


Friday, June 24, 2011 - 8:04:39 AM - Greg Robidoux Back To Top (14074)

Rickster I have not see this issue.  What version of SQL are you using and how many databases are you running this on?

Friday, June 24, 2011 - 8:03:47 AM - Greg Robidoux Back To Top (14073)

Thanks Marcio for the alternate approach.

Thursday, June 23, 2011 - 11:42:47 PM - marcio dalago Back To Top (14071)

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'')
    PRINT ''#'';
    EXEC #.dbo.sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)'',
    @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Index Updated''''''
END',@replaceChar = '#'

EXEC sp_msForEachDb @command1='PRINT ''?'';'  -- DATABASE
EXEC sp_msForEachTable @command1='PRINT ''?''' -- TABLES

Thursday, June 23, 2011 - 2:38:42 PM - Rickster Back To Top (14067)

I get the following error when running the author's script as shown:

Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'TableCursor' already exists.

Others aren't getting this?





Sunday, September 5, 2010 - 3:38:37 PM - marcio dalago Back To Top (10128)
hi, my sample is:


EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ''90'')'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Index Updated'''''' END',
@replaceChar = '#'

Friday, April 16, 2010 - 3:28:37 PM - Repriser2010 Back To Top (5257)

Not good enough. You have to query DMV first to find which ones are really in need of a rebuild, not just rebuild everything, because the resource usage will stall your server.

Monday, December 21, 2009 - 6:41:15 PM - admin Back To Top (4596)


That is a handy little trick if you have to manage multiple versions of SQL Server.

Monday, December 21, 2009 - 5:35:58 PM - LittlePanthers Back To Top (4595)

here you go, sorry forgot to uncomment out the actual 2k call.

if (@@microsoftversion / power(2, 24) >= 9)
       -- SQL 2005 command
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
       -- SQL 2000 command
       DBCC DBREINDEX(@Table,' ',@fillfactor) 
EXEC (@cmd)

Monday, December 21, 2009 - 5:34:48 PM - LittlePanthers Back To Top (4594)

to make it so you don't have to comment/uncomment lines of code, here is a tid bit I put in to run the script for either 2k or 2k5

if (@@microsoftversion / power(2, 24) >= 9)
       -- SQL 2005 command
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
       -- SQL 2000 command
       --DBCC DBREINDEX(@Table,' ',@fillfactor) 
EXEC (@cmd)

Friday, November 20, 2009 - 12:41:49 PM - daspeac Back To Top (4477)

thanks for your comment. I think that the recovery implies the rebuilding of entire database, the detailed description of procedure takes a lot of time, it can be read on the website of application developer

Monday, November 16, 2009 - 1:16:35 PM - admin Back To Top (4435)


It looks like the tool you mentioned is for recovering data\databases, but this tip is related to rebuilding indexes.  I believe these are two different needs.

Thank you,
The MSSQLTips Team

Monday, November 16, 2009 - 5:58:08 AM - daspeac Back To Top (4433)
Hi there, I believe there is an easier way to do it. try the waiting for sql server to recover databases program, it is a good solution, if you experience any difficulties with dbx files. This application also converts dbf files. Hope it helps

Friday, June 27, 2008 - 8:49:26 AM - sql_er Back To Top (1275)

When the indexes are being rebuilt, very often the transaction log grows, sometimes to a size you don't want it to grow to, as it might cause problems.  That is why, when I rebuilt indexes (in 1 database only), I usually rebuilt table by table, manually, checking the trans log size (using DBCC LOGSPACE) each time a big table with large indexes was rebuilt.

 If we are to automate it, I would think it might be helpful to add a test, such that after each index/table is rebuilt, check the transaction log space used.  If it is above a certain value, then either backup trans log, or just keep waiting and checking in a loop, until the scheduled trans log backup is executed, and the trans log space used drops below a certain value, and then continue with index rebuilding ...

Monday, June 2, 2008 - 5:03:23 AM - honzas Back To Top (1064)

It would be better to exclude databases in some non-operational states (e.g. offline, readonly etc.)

Just enhance the filtering of the cursor's query e.g. by following:

WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')    -- exlude system datbases
 AND status &  -- exclude statuses not suitable for reindexing
 (   32 -- loadig
  |  64 -- pre recovery
  | 128 -- recovering
  | 256 -- not recovered
  | 512 -- offline
  |1024 -- readonly
  |2048 -- dbo use only
  |4096 -- single user
  |32768 -- emergency mode
  ) = 0

Friday, April 4, 2008 - 7:41:39 AM - admin Back To Top (830)


Agreed and thank you for the post.  In our scenario, we did not have spaces in the database or table names, but from a best practices perspective it is a good idea to address the issue just it case it does occur.

Thank you,
The MSSQLTips.com Team

Monday, March 10, 2008 - 7:05:36 AM - ChipLee Back To Top (714)

There is a little problem in this script. When database name or table name contains space, this script will fail.

The modification is to add '[' and ']' before database name and table name

get free sql tips
agree to terms