全面优化数据库(重建/整理索引)
中国风 博客专家认证 2007-03-29 05:21:14 重建/整理索引
DECLARE @table_name sysname
DECLARE @index_name sysname
DECLARE @syntax sysname
DECLARE ROY_table CURSOR FOR
SELECT name FROM sysobjects where xtype = 'u'
OPEN ROY_table
FETCH NEXT FROM ROY_table INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ROY_index CURSOR FOR
select sysindexes.name
from sysindexes,sysobjects
where sysobjects.id = sysindexes.id and
sysobjects.name = @table_name and
keycnt > 0
OPEN ROY_index
FETCH NEXT FROM ROY_index INTO @index_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @syntax = 'DBCC INDEXDEFRAG (0, '+@table_name+','+ @index_name+')'
EXEC (@syntax)
PRINT '数据表'+@table_name + '索引'++@index_name+'碎片整理完成'
FETCH NEXT FROM ROY_index INTO @index_name
END
CLOSE ROY_index
DEALLOCATE ROY_index
FETCH NEXT FROM ROY_table INTO @table_name
END
CLOSE ROY_table
DEALLOCATE ROY_table