创建存储过程以禁用和启用索引

Question

下面有两个查询，首先，禁用索引，在另一个查询中启用索引。这是我的疑问

--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

我想知道是否有人能帮我为这两个查询创建两个存储过程？

Answer 1

最后，我设法为每个进程编写了一个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