羊窝-网站建设§程序开发§网站优化§网站策划

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