hot to rebuidl indexes

Hi,

I want to know how can I rebuild all indexes in all tables using T-SQL?

Thanks..

Hello Jassim,

This script will reindex all tables in a database. Just change the database name in the first line...

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @.TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Hope this helps! A full discussion of rebuilding indexes can be found here|||

Another command would be:

Code Snippet

use <dbname>

go

select 'DBCC DBREINDEX('''+object_name(id)+''','''',<sampling rate>)' from sys.sysindexes where xtype = 'U' and indid in (1,0) -- For SQL Server 2005

OR

use <dbname>

go

select 'DBCC DBREINDEX('''+object_name(id)+''','''',<sampling rate>)' from sysindexes where xtype = 'U' and indid in (1,0) -- For SQL Server 2000

The above commands would generate the T-SQL commands to re-index the tables of the database. You might want to use DBCC INDEXDEFRAG as this is an online operatio and DBREINDEX is an offline operation.