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

这篇文章主要为大家详细介绍了SQL Server 找出所有非xml索引并重新整理的sql，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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; 

注：关于SQL Server 找出所有非xml索引并重新整理的sql的内容就先介绍到这里，更多相关文章的可以留意