Stored Procedure to execute Drop and Rebuild Keys

sp_ExecDropAndRebuildSql


USE [Work]  --DB Name
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ExecDropAndRebuildSql]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ExecDropAndRebuildSql]
GO


USE [Work]  --DB Name
GO
CREATE PROCEDURE [dbo].[sp_ExecDropAndRebuildSql]


    @SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50), 
    @ACTION  VARCHAR(10),
    @KEY_TYPE VARCHAR(10)
    
AS  
DECLARE  
@SQL VARCHAR(max)
SET @SQL = 'param_error'


IF LTRIM(RTRIM(LOWER(@ACTION))) = 'drop'
BEGIN 
IF CHARINDEX('r',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [DropRefFK] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF CHARINDEX('f',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [DropFK] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF CHARINDEX('p',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [DropPK] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF CHARINDEX('i',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [DropIndex] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE 
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF @SQL='param_error'
RAISERROR ('The parameter key_type is not correct.',16,1)
END


ELSE IF LTRIM(RTRIM(LOWER(@ACTION))) = 'create'
BEGIN 
IF CHARINDEX('i',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [CreateIndex] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF CHARINDEX('p',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [CreatePK] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF CHARINDEX('f',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [CreateFK] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL


IF @@FETCH_STATUS <> 0
BREAK
ELSE
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF CHARINDEX('r',LTRIM(RTRIM(LOWER(@KEY_TYPE)))) <> 0
BEGIN
DECLARE CSR_STAT CURSOR FOR
SELECT [CreateRefFK] FROM [dbo].[TableConstraintMaintenance]   
WHERE   
[SchemaName] = CASE @SCHEMA_NAME WHEN '' THEN [SchemaName] ELSE @SCHEMA_NAME END
AND [TableName] = CASE @TABLE_NAME WHEN '' THEN [TableName] ELSE @TABLE_NAME END
OPEN CSR_STAT  
WHILE 1=1
BEGIN
FETCH NEXT FROM CSR_STAT   
INTO @SQL
IF @@FETCH_STATUS <> 0
BREAK
ELSE 
EXEC(@SQL)
END  
CLOSE CSR_STAT   
DEALLOCATE CSR_STAT
END
IF @SQL='param_error'
RAISERROR ('The parameter key_type is not correct.',16,1)
END
ELSE
RAISERROR ('The parameter action is not correct.',16,1)
GO