环思产品论坛

标题: 重建所有索引 [打印本页]

---

作者: zhouf    时间: 2014-12-22 09:14
标题: 重建所有索引


这个只产生重建索引的脚本，不执行。

1. SET NOCOUNT ON;
   
2. 
   
3. DECLARE @tbIndex TABLE(iIden INT IDENTITY(1,1) PRIMARY KEY
   
4.   ,sObjName NVARCHAR(255),sIndexName NVARCHAR(255));
   
5. INSERT INTO @tbIndex
   
6. SELECT b.name,a.name
   
7.   FROM sys.sysindexes a
   
8.   JOIN sys.objects b ON b.object_id=a.id
   
9.   WHERE a.indid IN (0,1) AND a.name IS NOT NULL AND b.type='u';
   
10. 
    
11. DECLARE @iIden INT=0,@sSQL NVARCHAR(2000)=0x,@rtn NVARCHAR(2)=NCHAR(13)+NCHAR(10);
    
12. WHILE EXISTS(SELECT * FROM @tbIndex)
    
13. BEGIN
    
14.   SELECT TOP 1 @iIden=iIden FROM @tbIndex;
    
15.   SELECT @sSQL=N'ALTER INDEX '+sIndexName+N' ON dbo.'+sObjName+N' REORGANIZE;'+@rtn+N'GO'+@rtn
    
16.     +N'ALTER INDEX '+sIndexName+' ON dbo.'+sObjName+' REBUILD PARTITION = ALL;'+@rtn+N'GO'+@rtn
    
17.     FROM @tbIndex WHERE iIden=@iIden;
    
18.   PRINT @sSQL;
    
19.   DELETE @tbIndex WHERE iIden=@iIden;
    
20. END;

复制代码

---

欢迎光临 环思产品论坛 (http://bbs.huansi.cn/)

Powered by Discuz! X3.2