178 lines
6.7 KiB
Transact-SQL
178 lines
6.7 KiB
Transact-SQL
-- Remove-WinLine_Temp_Tables
|
|
-- =================================================================
|
|
-- SQL Skript um verwaiste Temp Tabellen zu entfernen
|
|
--
|
|
-- =================================================================
|
|
-- Copyright (c) 2023 by Digital Data GmbH
|
|
--
|
|
-- Digital Data GmbH • Ludwig-Rinn-Strasse 16 • D-35452 Heuchelheim
|
|
-- Tel.: 0641/202360 • E-Mail: info-flow@digitaldata.works
|
|
-- =================================================================
|
|
-- Creation Date / Author: 09.03.2023 / MK
|
|
-- Version Date / Editor: 13.03.2023 / MK
|
|
-- Version Number: 2.0.0.0
|
|
-- =================================================================
|
|
-- History:
|
|
|
|
DECLARE @3_DIGIT_TABLE TABLE (id INT, name varchar(50))
|
|
DECLARE @5_DIGIT_TABLE TABLE (id INT, name varchar(50))
|
|
DECLARE @6_DIGIT_TABLE TABLE (id INT, name varchar(50))
|
|
DECLARE @ID INT,
|
|
@Name VARCHAR(50),
|
|
@Counter BIGINT = 0,
|
|
@TableSubString1 NVARCHAR(100),
|
|
@TableSubString2 NVARCHAR(100),
|
|
@DynamicSQL NVARCHAR(1000);
|
|
|
|
INSERT INTO @3_DIGIT_TABLE values(1,'Ansprechpartner'),(2,'BELEGETEMP'),(3,'BELEGKOPFTEMP'),(4,'BELEGZEILETEMP'),(5,'BSTTMP'),(6,'BSTTMPPARK'),(7,'BUCHTMP'),(8,'BUDGETSELTMP'),(9,'FB3NK'),(10,'FB3OPNR')
|
|
INSERT INTO @3_DIGIT_TABLE values(11,'G01W131'),(12,'G02W245COPY'),(13,'G02W245MCOPY'),(14,'G02W362K'),(15,'G02W362M'),(16,'G99W432X'),(17,'G99W458'),(18,'G99W458M'),(19,'G99W615DYN'),(20,'LAGERKOMMTMP')
|
|
INSERT INTO @3_DIGIT_TABLE values(21,'MESO530FAKTTMP'),(22,'PREISTMP'),(23,'T025TMP'),(24,'T026TMP'),(25,'TMP432_'),(26,'TMPANSPRECHPART_'),(27,'TMPGRID432_'),(28,'TMPGRID446_'),(29,'TMPGRID458_')
|
|
INSERT INTO @3_DIGIT_TABLE values(31,'VERTRETERBUCHTMP'),(32,'BUDGETTMP'),(33,'LAGERBUCHTMP'),(34,'G01W171'),(35,'G01W171K'),(36,'G01W172'),(37,'G02WG433FAKT'),(38,'TMP_AKTIONEN_WF_'),(39,'G02WG433FAKT')
|
|
INSERT INTO @3_DIGIT_TABLE values(41,'G02WG433FAKT'),(42,'G02W245M'),(43,'G11W4412A'),(44,'G99W670T'),(45,'G11W44A'),(46,'AUGTMP')
|
|
|
|
INSERT INTO @5_DIGIT_TABLE values(1,'LAGERBUCHTMP'),(2,'G99W774KOPF'),(3,'G99W774MITTE')
|
|
|
|
INSERT INTO @6_DIGIT_TABLE values(1,'G99W774KOPF'),(2,'G99W774MITTE'),(3,'G01W'),(4,'G99W'),(5,'G02W')
|
|
|
|
PRINT '------------------------------------------------------------'
|
|
PRINT 'START erste Schleife'
|
|
PRINT '------------------------------------------------------------'
|
|
|
|
DECLARE CURSOR_3_DIGIT_TABLE CURSOR
|
|
FOR
|
|
SELECT [ID], [NAME]
|
|
FROM @3_DIGIT_TABLE
|
|
|
|
OPEN CURSOR_3_DIGIT_TABLE
|
|
FETCH NEXT FROM CURSOR_3_DIGIT_TABLE INTO @ID, @Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
PRINT 'Table: ' + @Name
|
|
WHILE @COUNTER < 999
|
|
BEGIN
|
|
|
|
SET @counter = @counter+1
|
|
SET @TableSubString1 = @Name
|
|
SET @TableSubString2 = CASE LEN(@counter)
|
|
WHEN 1 THEN '00' + convert(VARCHAR(3),@counter)
|
|
WHEN 2 THEN '0' + convert(VARCHAR(3),@counter)
|
|
WHEN 3 THEN '' + convert(VARCHAR(3),@counter)
|
|
END;
|
|
|
|
PRINT 'Loop: ' + @TableSubString1 + @TableSubString2
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @TableSubString1 + @TableSubString2 +']') AND type in (N'U'))
|
|
BEGIN
|
|
PRINT '--> Removing Table: ' + @TableSubString1 + @TableSubString2
|
|
SET @DynamicSQL = 'DROP TABLE [dbo].[' + @TableSubString1 + @TableSubString2 +']'
|
|
EXEC (@DynamicSQL)
|
|
END
|
|
END
|
|
SET @Counter = 0
|
|
|
|
FETCH NEXT FROM CURSOR_3_DIGIT_TABLE INTO @ID, @Name
|
|
END
|
|
CLOSE CURSOR_3_DIGIT_TABLE
|
|
DEALLOCATE CURSOR_3_DIGIT_TABLE
|
|
|
|
PRINT '------------------------------------------------------------'
|
|
PRINT 'ENDE erste Schleife'
|
|
PRINT '------------------------------------------------------------'
|
|
|
|
PRINT '------------------------------------------------------------'
|
|
PRINT 'START zweite Schleife'
|
|
PRINT '------------------------------------------------------------'
|
|
|
|
DECLARE CURSOR_5_DIGIT_TABLE CURSOR
|
|
FOR
|
|
SELECT [ID], [NAME]
|
|
FROM @5_DIGIT_TABLE
|
|
|
|
OPEN CURSOR_5_DIGIT_TABLE
|
|
FETCH NEXT FROM CURSOR_5_DIGIT_TABLE INTO @ID, @Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
PRINT 'Table: ' + @Name
|
|
WHILE @COUNTER < 99999
|
|
BEGIN
|
|
|
|
SET @counter = @counter+1
|
|
SET @TableSubString1 = @Name
|
|
SET @TableSubString2 = CASE LEN(@counter)
|
|
WHEN 1 THEN '0000' + convert(VARCHAR(5),@counter)
|
|
WHEN 2 THEN '000' + convert(VARCHAR(5),@counter)
|
|
WHEN 3 THEN '00' + convert(VARCHAR(5),@counter)
|
|
WHEN 4 THEN '0' + convert(VARCHAR(5),@counter)
|
|
WHEN 5 THEN '' + convert(VARCHAR(5),@counter)
|
|
END;
|
|
|
|
PRINT 'Loop: ' + @TableSubString1 + @TableSubString2
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @TableSubString1 + @TableSubString2 +']') AND type in (N'U'))
|
|
BEGIN
|
|
PRINT '--> Removing Table: ' + @TableSubString1 + @TableSubString2
|
|
SET @DynamicSQL = 'DROP TABLE [dbo].[' + @TableSubString1 + @TableSubString2 +']'
|
|
EXEC (@DynamicSQL)
|
|
END
|
|
END
|
|
SET @Counter = 0
|
|
|
|
FETCH NEXT FROM CURSOR_5_DIGIT_TABLE INTO @ID, @Name
|
|
END
|
|
CLOSE CURSOR_5_DIGIT_TABLE
|
|
DEALLOCATE CURSOR_5_DIGIT_TABLE
|
|
|
|
PRINT ''
|
|
PRINT '------------------------------------------------------------'
|
|
PRINT 'ENDE zweite Schleife'
|
|
PRINT '------------------------------------------------------------'
|
|
|
|
PRINT '------------------------------------------------------------'
|
|
PRINT 'START dritte Schleife'
|
|
PRINT '------------------------------------------------------------'
|
|
|
|
DECLARE CURSOR_6_DIGIT_TABLE CURSOR
|
|
FOR
|
|
SELECT [ID], [NAME]
|
|
FROM @6_DIGIT_TABLE
|
|
|
|
OPEN CURSOR_6_DIGIT_TABLE
|
|
FETCH NEXT FROM CURSOR_6_DIGIT_TABLE INTO @ID, @Name
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
PRINT 'Table: ' + @Name
|
|
WHILE @COUNTER < 999999
|
|
BEGIN
|
|
|
|
SET @counter = @counter+1
|
|
SET @TableSubString1 = @Name
|
|
SET @TableSubString2 = CASE LEN(@counter)
|
|
WHEN 1 THEN '00000'+ convert(VARCHAR(6),@counter)
|
|
WHEN 2 THEN '0000' + convert(VARCHAR(6),@counter)
|
|
WHEN 3 THEN '000' + convert(VARCHAR(6),@counter)
|
|
WHEN 4 THEN '00' + convert(VARCHAR(6),@counter)
|
|
WHEN 5 THEN '0' + convert(VARCHAR(6),@counter)
|
|
WHEN 6 THEN '' + convert(VARCHAR(6),@counter)
|
|
END;
|
|
|
|
PRINT 'Loop: ' + @TableSubString1 + @TableSubString2
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @TableSubString1 + @TableSubString2 +']') AND type in (N'U'))
|
|
BEGIN
|
|
PRINT '--> Removing Table: ' + @TableSubString1 + @TableSubString2
|
|
SET @DynamicSQL = 'DROP TABLE [dbo].[' + @TableSubString1 + @TableSubString2 +']'
|
|
EXEC (@DynamicSQL)
|
|
END
|
|
END
|
|
SET @Counter = 0
|
|
|
|
FETCH NEXT FROM CURSOR_6_DIGIT_TABLE INTO @ID, @Name
|
|
END
|
|
CLOSE CURSOR_6_DIGIT_TABLE
|
|
DEALLOCATE CURSOR_6_DIGIT_TABLE
|
|
|
|
PRINT ''
|
|
PRINT '------------------------------------------------------------'
|
|
PRINT 'ENDE dritte Schleife'
|
|
PRINT '------------------------------------------------------------'
|