复制代码 代码如下: DECLARE cur CURSOR FOR SELECT [object_name]=s.name+'.'+OBJECT_NAME(A.object_id), B.name FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,null,null,null) AS A JOIN sys.indexes AS B ON A.[object_id]=
复制代码 代码如下:DECLARE cur CURSOR FOR SELECT [object_name]=s.name+'.'+OBJECT_NAME(A.object_id), B.name FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,null,null,null) AS A JOIN sys.indexes AS B ON A.[object_id]=B.[object_id] AND A.[index_id]=B.[index_id] JOIN sys.objects AS o ON A.[object_id]=o.[object_id] JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id] WHERE A.[index_id]>0 AND NOT EXISTS( SELECT * FROM sys.xml_indexes WHERE A.[object_id]=[object_id] AND A.[index_id]=[index_id] ); OPEN cur; DECLARE @objname varchar(128),@indname varchar(128); DECLARE @sql nvarchar(4000); FETCH NEXT FROM cur INTO @objname,@indname; --重整所有索引，在这里先不管索引的碎片程度 WHILE @@FETCH_STATUS=0 BEGIN SET @sql='ALTER INDEX '+@indname+' ON '+@objname+' REBUILD'; EXEC(@sql); FETCH NEXT FROM cur INTO @objname,@indname; END CLOSE cur; DEALLOCATE cur;
