---- Arrangedb.sql Reorganization Database ---- Copyrights @ 2003.12.29 Digital China Management System Co., Ltd .---- Renest Process: Add all user tables to the Clustered index, so SQL Server will reorganize Table, then delete the index ---- Using the timing: daily maintenance, it is recommended to make a reorganization once a week. ---- Notes: Please do database reception, update statistics, re-index, and integrity checks
--Drop Table # DCMS_INDEX_INFO - The temporary table to use create table #dcms_index_info (index_name sysname, index_description varchar (210), INDEX_KEYS VARCHAR (2078))
- Set the environment set nocount on - Program start: Variable Declare @SQL VARCHAR (500) Declare @tablename varchar (20) Declare @erroSave Int
- Handling Declare Cursor1 Cursorfor Select Name from sysobjects where xtype = 'u' Order by Name
Open cursor1fetch next from cursor1 inTo @tablename
While @@ fetch_status = 0begin - In order to detect that there is a crete_date in the table, Yilifei's table is there, if not skipping, but dealing with it - should have a better way, I temporarily use it. . Don't want to use sp_columns because you don't want to build a table, slow down if columnproperty (Object_ID (@tablename), 'create_date', 'precision') Is Not Null Begin Print 'Process Table' @tablename
- Get the index information of the current table delete #dcms_index_info set @sql = 'sp_helpindex' '' 'INSERT INTO #dcms_index_info exec (@SQL)
- Finding is already a clustered index (clustered), if you already have it, there is a clustered index in Yi Fei is a few select index_name from #dcms_index_info where index_description like 'clustered%'
IF @@ rowcount = 0 begin set @SQL = 'create clustered index dctempindex001 on' @tablename '(create_date)' Exec (@SQL)
Set @SQL = 'Drop Index' @tablename '.dctempindex001 'Exec (@SQL) end end
