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

 

 

--找出所有非xml索引
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;