創建儲存過程以禁用和啟用索引

我有兩個以下查詢，首先，禁用索引並在另一個查詢中啟用它們。這是我的查詢

--Disabling indexes
DECLARE @my_sql2 NVARCHAR(200);
DECLARE cur_rebuild CURSOR FOR 
SELECT 'ALTER INDEX ' +  i.name + ' ON ' + t.name + ' DISABLE' 
FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_disabled = 1 ORDER BY t.name, i.name;

OPEN cur_rebuild;

FETCH NEXT FROM cur_rebuild INTO @my_sql2;

WHILE @@FETCH_STATUS = 0

BEGIN

  EXECUTE sp_executesql  @my_sql2;

  FETCH NEXT FROM cur_rebuild INTO @my_sql2;

END;

CLOSE cur_rebuild;

DEALLOCATE cur_rebuild;

GO

和

--rebuilding indexes
DECLARE @my_sql2 NVARCHAR(200);

DECLARE cur_rebuild CURSOR FOR 

SELECT 'ALTER INDEX ' +  i.name + ' ON ' + t.name + ' REBUILD' 
FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id 
WHERE i.is_disabled = 1 ORDER BY t.name, i.name;

OPEN cur_rebuild;

FETCH NEXT FROM cur_rebuild INTO @my_sql2;

WHILE @@FETCH_STATUS = 0

BEGIN

  EXECUTE sp_executesql  @my_sql2;

  FETCH NEXT FROM cur_rebuild INTO @my_sql2;

END;

CLOSE cur_rebuild;

DEALLOCATE cur_rebuild;

GO

我想知道是否有人可以幫助我為這兩個查詢創建兩個儲存過程？

最後，我設法為每個程序編寫了一個 SP。

這是禁用索引：

CREATE PROCEDURE [dbo].[DisableNonClusteredIndexes]
@tableName VARCHAR(100)
AS
SET nocount ON;
DECLARE @my_sql2 NVARCHAR(200);
DECLARE cur_rebuild CURSOR FOR 
SELECT 'ALTER INDEX ' +  i.name + ' ON ' + t.name + ' DISABLE'
FROM sys.indexes I 
JOIN sys.tables t ON i.object_id = t.object_id 
WHERE i.is_disabled = 1 and t.name= @tableName
ORDER BY t.name, i.name;
OPEN cur_rebuild;
FETCH NEXT FROM cur_rebuild INTO @my_sql2;
WHILE @@FETCH_STATUS = 0
BEGIN
   EXECUTE sp_executesql  @my_sql2;
   FETCH NEXT FROM cur_rebuild INTO @my_sql2;
END;
CLOSE cur_rebuild;
DEALLOCATE cur_rebuild;
GO

這是啟用/重建索引：

CREATE PROCEDURE [dbo].[EnableNonClusteredIndexes]
 @tableName VARCHAR(100)
AS
SET nocount ON;
DECLARE @my_sql2 NVARCHAR(200);
DECLARE cur_rebuild CURSOR FOR 
 SELECT 'ALTER INDEX ' +  i.name + ' ON ' + t.name + ' REBUILD' 
 FROM sys.indexes I 
 JOIN sys.tables t ON i.object_id = t.object_id 
 WHERE i.is_disabled = 1 and t.name = @tableName
 ORDER BY t.name, i.name;
OPEN cur_rebuild;
FETCH NEXT FROM cur_rebuild INTO @my_sql2;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXECUTE sp_executesql  @my_sql2;
  FETCH NEXT FROM cur_rebuild INTO @my_sql2;
END;
CLOSE cur_rebuild;
DEALLOCATE cur_rebuild;
GO

引用自：https://dba.stackexchange.com/questions/273895