找出所有非xml索引并重新整理的sql

复制代码 代码如下:

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;