SQL SERVER Management Studio – Rebuild All Indexes on Table

Today, we are going to see a very simple trick about SQL SERVER Management Studio (SSMS), where we will learn how to rebuild all indexes on the table with a single click. While I considered this as a very simple trick while demonstrating this to my e-commerce client during the Comprehensive Database Performance Health Check, they were very surprised to see this option.

SQL SERVER Management Studio - Rebuild All Indexes on Table rebuildall-800x222

If you want to rebuild all the indexes on any particular table quickly, you can just do that with the help of SSMS. Just go to the table, further expand the indexing folder and right after that you can right-click on it and select the option to rebuild all the indexes. Here is the image for additional clarity.

SQL SERVER Management Studio - Rebuild All Indexes on Table rebuild-all1

Once you select that option it will bring up the following screen.

SQL SERVER Management Studio - Rebuild All Indexes on Table rebuild-all2

Over here it will display all the indexes of the table and you can just click OK and it will rebuild all the indexes. You can use the same screen to see the various details about indexes. On the same page, you will see the Total Fragmentation as well.

It is quite possible that not every time you want to use SSMS. There are chances when you just want to generate the script and execute that script. In that case, you can use the same screen to generate the script to rebuild all of your indexes as well.

Once you generate the script you can execute them in the SSMS query window.

Well, while the tip looks very simple, trust me not many people know about it. If you know any such tricks, please post them in the query window and I will be happy to post on the blog with the due credit to you. Meanwhile, If you have ever attended any of my online sessions, you might be familiar with my opinion that I do not like more than 7 indexes on any table. You can read more about that in this blog post: SQL SERVER – Poor Indexing Strategies – 10 Don’ts for Indexes

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Scripts, SQL Server, SQL Server Management Studio, SSMS
Previous Post
SQL SERVER – Count NULL Values From Column
Next Post
SQL SERVER – Msg 17177 – This Instance of SQL Server Has Been Using a Process ID of %s since %s (local) %s (UTC)

Related Posts

13 Comments. Leave new

  • Mohammed Benabdallah
    March 7, 2020 1:31 am

    Quick and easy tip, thanks for sharing

    Reply
  • Christopher D Wolff
    March 7, 2020 8:20 pm

    Be very careful with this if you are in an AG on the cloud. This may cause the secondary to get behind if your indexes are over 1 million pages. The main issue being the through put from the disk on the cloud providers

    Reply
  • Malcolm K Crowe
    March 8, 2020 12:35 am

    It’s a disgrace. You wouldn’t need it if the engine kept things consistent. If the indexes are wrong, how do you know whether the tables have been hacked?

    Reply
  • Kind of a pity you would have to resort to this at all, you would expect the indexing component to be able to optimise this for you.

    Reply
  • Anthony Griggs
    March 8, 2020 7:43 am

    I’m sure this probably sounds like a silly question to you but why would you want to rebuild the indexes? Obviously they already exist so what is the advantage to rebuild them again?

    Reply
    • If there are lots of insert/update/delete the index gets fragmentation and it needs to be rebuild to optimize the read.

      Reply
  • I have a situation, when i do rebuild on specific table is taking 11 minutes, but when i do rebuild on all tables is taking 3 minute. Why the first once is taking more than second one. Here i done rebuild offline . For the both cases i restore the database every time.

    Reply
  • What if a table does not rebuild its index, even though I’ve tried over and over??

    Reply
  • Carlos Ignacio Aguero
    September 20, 2023 9:31 pm

    After to restore databases, I have to rebuild index o not?

    Reply

Leave a Reply