SQL Query to Re-build Indexes

Question

Hi

Is there any SQL query equivalent to Re-building the indexes. In SQL Server Management Studio Select desired DB and select the table, click on the "+" symbol to expand the table properties, in that select indexes, if already indexes exists it will list. Right click the index you can see "Rebuild " option

What i want is the this option to achieve in SQL Query

Thanks Chetan

Answer 1

ALTER INDEX {index_name} ON {table} REBUILD PARTITION = ALL
WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

You can get at the script by selecting the "script" button in the window that comes up after you select "Rebuild".

Answer 2

Michelle Ufford (aka "SQL Fool") has a great repository of scripts:

http://sqlfool.com/category/sql-scripts/

Her Index Defrag Script v4.0 is probably the best around and it's well tested. You can let it loose on your entire database, and it will sniff out those indices in need of REBUILD (defrag) or REINDEX (complete re-creation).

Answer 3

I like the Ola Hallengren scripts for index (and general database) maintenance.

http://ola.hallengren.com/