sql-scripts/DELPHIX - clean schemas.sql

USE HCITools

IF NOT EXISTS(SELECT 1 FROM sys.[schemas] s WHERE s.[name]='delphix')
BEGIN 
    EXEC('CREATE SCHEMA [delphix]');
END 
GO

IF OBJECT_ID('delphix.arizonaCust_cleanup') IS NOT NULL 
BEGIN
    DROP PROCEDURE delphix.arizonaCust_cleanup;
END
GO

/*
OC-945

This procedure is used by Delphix to delete every data in arizonaCust.
As the schema is not unified through all the pharmacies, Delphix cannot be used.

We have received a confirmation from Gilles Balanche on the 15 of Mai 2024 that 
arizonaCust in N+1 and N+2 env are not used, and that data can be deleted.
*/
CREATE PROCEDURE delphix.arizonaCust_cleanup
AS
BEGIN
    --deletion of data in arizonaCust
    IF EXISTS(
        SELECT 1
        FROM [master].[cfg].[InstanceContext] c
        WHERE (
            [type]    = 'VALI' --N+1
            OR [type] = 'DEVE' --N+2
        )
    )
    BEGIN
        DECLARE @tbl_name VARCHAR(111), @schema_name VARCHAR(11);
        DECLARE @tplTruncate VARCHAR(MAX), @query VARCHAR(MAX);
        SET @tplTruncate='
BEGIN TRY
    TRUNCATE TABLE [arizonaCust].[@schema@].[@tbl@];
END TRY
BEGIN CATCH
    DELETE FROM [arizonaCust].[@schema@].[@tbl@];
END CATCH 

';
        
        DECLARE csr_tbl CURSOR FAST_FORWARD READ_ONLY FOR 
            SELECT [d].[name], s.[name] as [schema_name]
            FROM [ArizonaCUST].[sys].[tables] d
                INNER JOIN [ArizonaCUST].[sys].[schemas] s on s.[schema_id] = d.[schema_id]

        OPEN csr_tbl

        FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @query = REPLACE(
                REPLACE(
                    @tplTruncate
                    ,'@schema@'
                    ,@schema_name
                )
                ,'@tbl@'
                ,@tbl_name
            );

            PRINT @query;
            EXEC(@query);

            FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name
        END

        CLOSE csr_tbl
        DEALLOCATE csr_tbl


    END 

END 
go

IF OBJECT_ID('delphix.hciTool_cleanup') IS NOT NULL 
BEGIN
    DROP PROCEDURE delphix.hciTool_cleanup;
END
GO

/*
OC-945

This procedure is used by Delphix to delete every data in the TMP schema of hciTools.
As the schema is not unified through all the pharmacies, Delphix cannot be used.
*/
CREATE PROCEDURE delphix.hciTool_cleanup
AS
BEGIN
        DECLARE @tbl_name VARCHAR(111), @schema_name VARCHAR(11);
        DECLARE @tplTruncate VARCHAR(MAX), @query VARCHAR(MAX);
        SET @tplTruncate='
BEGIN TRY
    TRUNCATE TABLE [hciTools].[@schema@].[@tbl@];
END TRY
BEGIN CATCH
    DELETE FROM [hciTools].[@schema@].[@tbl@];
END CATCH 

';
        
        DECLARE csr_tbl CURSOR FAST_FORWARD READ_ONLY FOR 
            SELECT [d].[name], s.[name] as [schema_name]
            FROM [HCITools].[sys].[tables] d
                INNER JOIN [HCITools].[sys].[schemas] s on s.[schema_id] = d.[schema_id]
            WHERE schema_name(d.schema_id)='TMP'

        OPEN csr_tbl

        FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @query = REPLACE(
                REPLACE(
                    @tplTruncate
                    ,'@schema@'
                    ,@schema_name
                )
                ,'@tbl@'
                ,@tbl_name
            );

            PRINT @query;
            EXEC(@query);

            FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name
        END

        CLOSE csr_tbl
        DEALLOCATE csr_tbl

END
GO

IF OBJECT_ID('delphix.arizonaLD_cleanup') IS NOT NULL 
BEGIN
    DROP PROCEDURE delphix.arizonaLD_cleanup;
END
GO

/*
OC-945

This procedure is used by Delphix to delete every data in arizonaLD database.
As the schema is not unified through all the pharmacies, Delphix cannot be used.
*/
CREATE PROCEDURE delphix.arizonaLD_cleanup
AS
BEGIN
        DECLARE @tbl_name VARCHAR(111), @schema_name VARCHAR(11);
        DECLARE @tplTruncate VARCHAR(MAX), @query VARCHAR(MAX);
        SET @tplTruncate='
BEGIN TRY
    TRUNCATE TABLE [arizonaLD].[@schema@].[@tbl@];
END TRY
BEGIN CATCH
    DELETE FROM [arizonaLD].[@schema@].[@tbl@];
END CATCH 

';
        
        DECLARE csr_tbl CURSOR FAST_FORWARD READ_ONLY FOR 
            SELECT [d].[name], s.[name] as [schema_name]
            FROM [arizonaLD].[sys].[tables] d
                INNER JOIN [arizonaLD].[sys].[schemas] s on s.[schema_id] = d.[schema_id]

        OPEN csr_tbl

        FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @query = REPLACE(
                REPLACE(
                    @tplTruncate
                    ,'@schema@'
                    ,@schema_name
                )
                ,'@tbl@'
                ,@tbl_name
            );

            PRINT @query;
            EXEC(@query);

            FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name
        END

        CLOSE csr_tbl
        DEALLOCATE csr_tbl

END
GO

IF OBJECT_ID('delphix.arizona_cleanup') IS NOT NULL 
BEGIN
    DROP PROCEDURE delphix.arizona_cleanup;
END
GO

/*
OC-945

This procedure is used by Delphix to delete content of tables named TT% or ZZ% in the arizona database.
As the schema is not unified through all the pharmacies, Delphix cannot be used.
*/
CREATE PROCEDURE delphix.arizona_cleanup
AS
BEGIN
        DECLARE @tbl_name VARCHAR(111), @schema_name VARCHAR(11);
        DECLARE @tplTruncate VARCHAR(MAX), @query VARCHAR(MAX);
        SET @tplTruncate='
BEGIN TRY
    TRUNCATE TABLE [arizona].[@schema@].[@tbl@];
END TRY
BEGIN CATCH
    DELETE FROM [arizona].[@schema@].[@tbl@];
END CATCH 

';
        
        DECLARE csr_tbl CURSOR FAST_FORWARD READ_ONLY FOR 
            SELECT [d].[name], s.[name] as [schema_name]
            FROM [arizona].[sys].[tables] d
                INNER JOIN [Arizona].[sys].[schemas] s on s.[schema_id] = d.[schema_id]
            WHERE [d].[name] LIKE 'TT[_]%'
               OR [d].[name] LIKE 'ZZ[_]%'

        OPEN csr_tbl

        FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @query = REPLACE(
                REPLACE(
                    @tplTruncate
                    ,'@schema@'
                    ,@schema_name
                )
                ,'@tbl@'
                ,@tbl_name
            );

            PRINT @query;
            EXEC(@query);

            FETCH NEXT FROM csr_tbl INTO @tbl_name, @schema_name
        END

        CLOSE csr_tbl
        DEALLOCATE csr_tbl

END
GO
begin transaction
exec hcitools.delphix.arizonaCust_cleanup;
exec hcitools.delphix.hciTool_cleanup;
exec hcitools.delphix.arizonaLD_cleanup;
exec hcitools.delphix.arizona_cleanup;
rollback transaction