SQL Server database maintenance and rebuild index

Step one: See if maintenance is required to see if scan density/scan density is 100%

1. DECLARE @table_id int
2. SET @table_id=object_id (' table name ')
3. DBCC SHOWCONTIG (@table_id)

Step two: Refactor SQL Server database table indexes

1. DBCC DBREINDEX (' table name ', PK_ index name, 100)

Redo the first step, such as finding the scan density/scan density or less than 100% to refactor all indexes of the table, does not necessarily reach 100%.

1. DBCC DBREINDEX (' table name ', ' ', 100 ')

Rebuilding indexes on all tables in a database

Use My_database; DECLARE @name varchar (100)

DECLARE authors_cursor cursor for Select [name] from sysobjects where xtype= ' u ' ORDER by ID

OPEN Authors_cursor

FETCH NEXT from Authors_cursor to @name

While @ @FETCH_STATUS = 0 BEGIN

DBCC Dbreindex (@name, ", 90)

FETCH NEXT from Authors_cursor to @name END

Deallocate authors_cursor

Reference:

DBCC showcontig (Transact-SQL) http://msdn.microsoft.com/zh-cn/library/ms175008 (v=sql.90). aspx

DBCC dbreindex (Transact-SQL) http://msdn.microsoft.com/zh-cn/library/ms181671 (v=sql.90). aspx

SQL Server database maintenance and rebuild index