hot rebuild all indexes

how can run a command to rebuild all indexes in a database?

Do you want to just update the statistics or rebuild the indexes? The latter does more than just update statistics of the index. You can use sp_updatestats to rebuild statistics for all tables in the current database. There is no equivalent one for rebuilding indexes however and you will have to write your own. Lastly, you do not want to perform both these operations without knowing the ramifications. Check out the blog from the SQL Server Storage Engine team for more details on these operations and also a whitepaper:

http://blogs.msdn.com/sqlserverstorageengine/default.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

|||hi
you can use ALTER INDEX for reindexing.
i think if you use from "Indexed View" or "Covering Index" then your speed is very high.
good luck|||It depends. Using indexed views or adding more columns in an index to cover a query can adversely affect DML operations on the base tables. You need to outweigh the pros and cons before using indexed views or creating a covered index. In SQL Server 2005, we also have the ability to INCLUDE columns to non-clustered indexes at the leaf level so that they are not part of the key column(s). This has slightly better performance benefits than adding columns to the index key(s). This is another option to consider and evaluate.|||

Hi,

It is neccesary to check the Index statistics to measure the health of an Index.In Order to see statistics of any index follow the sample T-SQL command you will need to run:

DECLARE

@.ID int,

@.IndexID int,

@.IndexName varchar(128)

--input your table and indexname

SELECT @.IndexName = 'AK_DepartmentName'

SET @.ID = OBJECT_ID('HumanResources.Department')

SELECT @.IndexID = IndID

From sysindexes

Where id = @.ID AND name = @.IndexName

--run the DBCC Command

DBCC SHOWCONTIG( @.id, @.IndexID)

Note:DBCC-->Database Consistency Checker is used for checking lots of entities in SQL Server.

But, as per your requirement you can also run "DBCC SHOW STATISTICS" to see when was the last time the indexes were rebuild.

The same example as above,

DBCC SHOW_STATISTICS ('Humanresources.department' , 'PK_Department_DepartmentID')

After this, you can Reorganize your index using "DBCC DBREINDEX".You can either request a particular index to be re-organized or just re-index all the indexes of the table.

The same example of HumanResources.Department.

--This will Re-index all your indexes belonging to "HumanResources.Department".

DBCC DBREINDEX ([HumanResources.Department])

--This will Re-Index only "AK_Department_Name"

DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name])

--This will Re-index with a "Fill factor"

DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name],70)

You can then again run DBCC SHOWCONTIG as in the first sample code to see the results.