手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >找出所有非xml索引并重新整理的sql
找出所有非xml索引并重新整理的sql
摘要:复制代码代码如下:DECLAREcurCURSORFORSELECT[object_name]=s.name+'.'+OBJECT_NAME...

复制代码 代码如下:

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;

【找出所有非xml索引并重新整理的sql】相关文章:

sqlserver 一张表两列都的值都相同处理

SQL Server 索引维护sql语句

SQL查找某一条记录的方法

Microsoft SQLServer的版本区别及选择

sqlserver 游标的简单示例

简述SQL Server 2005数据库镜像相关知识

DBNull和Null的区别

sql2005创建远程登录帐户的sql语句

在SQL Server启动时自动执行存储过程。第1/2页

SQLServer 数据集合的交、并、差集运算

精品推荐
分类导航