廣告：

CREATE PROCEDURE RebuildAllIndexes
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @TableName NVARCHAR(128);
 DECLARE @Command NVARCHAR(4000);
 
 DECLARE TableCursor CURSOR FOR 
 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
 
 OPEN TableCursor
 
 FETCH NEXT FROM TableCursor INTO @TableName
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @Command = N'ALTER INDEX ALL ON ' + @TableName + ' REBUILD';
 EXEC sp_executesql @Command
 
 FETCH NEXT FROM TableCursor INTO @TableName
 END
 
 CLOSE TableCursor
 DEALLOCATE TableCursor
END
GO

EXEC RebuildAllIndexes;

第二種方法:

IF OBJECT_ID('REBUILD_ALL_INDEX_FOR_MSSQL') IS NOT NULL DROP PROC REBUILD_ALL_INDEX_FOR_MSSQL;

go

CREATE PROCEDURE [dbo].[REBUILD_ALL_INDEX_FOR_MSSQL]
AS
 DECLARE @tablename varchar(100);
 DECLARE @indexname varchar(300);
BEGIN
 print 'REBUILD_ALL_INDEX_FOR_MSSQL - 20190427.1'
 print 'Challenge Your Won, Challengy Won'
 print ' '
 declare _table cursor for select name from sysobjects where xtype='U' order by name
 OPEN _table
 fetch next from _table into @tablename
 while (@@fetch_status=0)
 begin

 declare _index cursor for select i.name from sysindexes i JOIN sysobjects c ON i.id = c.id WHERE i.indid NOT IN ( 0, 255 ) and i.root is not null AND c.name = @tablename ORDER BY i.name
 open _index
 fetch next from _index into @indexname
 while (@@fetch_status=0)
 begin

  print convert(varchar(20),getdate(),120)+' exec -> ALTER INDEX '+@indexname+' on '+@tablename+' REBUILD'
  exec('ALTER INDEX '+@indexname+' on '+@tablename+' REBUILD')
  print convert(varchar(20),getdate(),120)
  print ' '

 fetch next from _index into @indexname
 end
 close _index
 deallocate _index

 fetch next from _table into @tablename
 end
 close _table
 deallocate _table
END

go

EXEC [dbo].[REBUILD_ALL_INDEX_FOR_MSSQL];

go

廣告：