全面优化数据库(重建/整理索引
重建/整理索引
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
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
作者: 1937 发布时间: 2007-12-01
感谢ing~~~学习ing~~~~
作者: petterkelly 发布时间: 2008-01-27
参考参考。。。。。
作者: gzmong 发布时间: 2008-01-30
300多张表重建索引用代码建 应该会搞S人的哦
作者: jiaer36 发布时间: 2009-07-21
学习学习!!!
作者: fuxiaoyang13 发布时间: 2009-09-02
学习中。。。谢谢!
作者: dingyubao 发布时间: 2011-02-10