MSSQL Rebuild(重建)索引

DECLARE @tablename VARCHAR(50)
DECLARE @indexname VARCHAR(50)
DECLARE @cmdsql NVARCHAR(MAX)
DECLARE index_cursor CURSOR
FOR
SELECT  OBJECT_NAME(object_id) AS Table_Name ,
        name
FROM    sys.indexes
WHERE   name IS NOT NULL
        AND OBJECT_NAME(object_id) IN ( SELECT  name
                                        FROM    sys.tables )
          OPEN index_cursor
FETCH NEXT FROM index_cursor
INTO @tablename,@indexname

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmdsql ='ALTER INDEX ['+@indexname+'] ON [dbo].['+@tablename+'] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )'
        EXECUTE sp_executesql @cmdsql
        PRINT @cmdsql
        FETCH NEXT FROM index_cursor
 INTO @tablename,@indexname
    END
CLOSE index_cursor
DEALLOCATE index_cursor

第二种方式

 
-- 指定你要重建索引的数据库
USE TestDb
GO
 
DECLARE @table_name VARCHAR(200)
 
--查询数据库里的所有表名
DECLARE cursor_reindex CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.tables
 
--使用游标遍历表列表
OPEN cursor_reindex
 
FETCH NEXT FROM cursor_reindex INTO @table_name
 
WHILE @@FETCH_STATUS = 0
BEGIN
    --开始重建表索引
	DBCC DBREINDEX(@table_name)
	--DBCC DBREINDEX(@table_name, '', 90)
 
    FETCH NEXT FROM cursor_reindex INTO @table_name
END
 
CLOSE cursor_reindex
DEALLOCATE cursor_reindex

---

转载地址：https://www.cnblogs.com/amylis_chen/p/3369451.html