USE [Gaia]
GO
SET NOCOUNT ON;


DECLARE @Pharamcycode    char(6),
        @Pharmacyalias   char(9),
        @fqdn            varchar(30),
        @Backuppath      varchar(50),
        @DROP            bit,
        @sqlstmt         nvarchar(max),
        @SQLpath         varchar(30)



/* !!! SET ALL VARIABLES HERE !!! */
SET @Pharmacyalias = 'AMA245APS' /* EX: AMA208APS*/
SET @fqdn = 'ama245aps.amavita.ch' /* EX: ama208aps.amavita.ch */
SET @Pharamcycode = 'AAM245' /* EX: AAM208 */

SET @SQLpath = 'D:\SQLDatabase\' /* PROD = D:\SQLDatabase\ N1 = D:\SQLDatabase\ N2 = F:\SQLDatabase\ */

PRINT 'USE [Gaia]
';

/* DROP CERES ROUTE */
PRINT('DROP ROUTE [//' + @Pharmacyalias + '/APSSQL/Ceres]')

/* find active columnstore indexes */
DECLARE @tCsIdx TABLE(
    [schemaName] VARCHAR(50),
    [tableName] VARCHAR(100),
    [indexName] VARCHAR(100),
    [indexType] VARCHAR(50),
    [disableCmd] AS 'ALTER INDEX ['+[indexName]+'] ON ['+[schemaName]+'].['+[tableName]+'] DISABLE;'+CHAR(13)+CHAR(10),
    [enableCmd] AS 'ALTER INDEX ['+[indexName]+'] ON ['+[schemaName]+'].['+[tableName]+'] REBUILD;'+CHAR(13)+CHAR(10)
);

INSERT INTO @tCsIdx ([schemaName], [tableName], [indexName], [indexType])

SELECT
    OBJECT_SCHEMA_NAME([i].[object_id]) AS [SchemaName]
   ,OBJECT_NAME([i].[object_id])        AS [TableName]
   ,[i].[name]                          AS [IndexName]
   ,[i].[type_desc]                     AS [IndexType]
  FROM [sys].[indexes] AS [i]
 WHERE [i].[is_hypothetical] = 0
 AND [i].[index_id]          <> 0
 AND [i].[type_desc] IN ( 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE' )
 AND i.[is_disabled] = 0
;

/* disable columnstore indexes */
DECLARE @csIdxCmd VARCHAR(MAX)='';
SELECT @csIdxCmd = @csIdxCmd + [tci].[disableCmd]
FROM @tCsIdx [tci];
PRINT @csIdxCmd;
PRINT ''

/* PURGE OLD DATA */
SELECT 'DELETE FROM [' + s.name + '].[' + t.name + '] WHERE ' + c.name + ' = ''' + @pharamcycode + ''';' as delcmd
  INTO #AllPurge
  FROM sys.schemas s
  JOIN sys.tables t
   ON t.schema_id = s.schema_id
  JOIN sys.columns c
    ON c.object_id = t.object_id
   AND c.name LIKE '%_pharmacy_code'
 WHERE s.name = 'phar'
 ORDER BY t.name

DECLARE c_alldelcmd CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT * FROM #AllPurge
OPEN c_alldelcmd
FETCH NEXT FROM c_alldelcmd
INTO @sqlstmt

WHILE @@fetch_status = 0
BEGIN

 PRINT(@sqlstmt)

FETCH NEXT FROM c_alldelcmd
INTO @sqlstmt
END

CLOSE c_alldelcmd
DEALLOCATE c_alldelcmd

/* DROP PARTITION */
PRINT'ALTER PARTITION FUNCTION [PF_PharmacyCode] () MERGE RANGE (''' + @pharamcycode + ''');'
PRINT'ALTER DATABASE [Gaia] REMOVE FILE [GAIA_' + @pharamcycode + ']'
PRINT'ALTER DATABASE [Gaia] REMOVE FILEGROUP [FG_GAIA_' + @pharamcycode + ']'
PRINT ''

/* re-enable columnstore indexes */
SET @csIdxCmd = ''
SELECT @csIdxCmd = @csIdxCmd + [tci].[enableCmd]
FROM @tCsIdx [tci];
PRINT @csIdxCmd;
PRINT ''

DROP TABLE #AllPurge
GO