AppStd/Skriptentwickung
8
0
Fork 0
Code
Skriptentwickung/archive/Remove-WinLine_Temp_Tables/Archiv/1.0.0.0/Remove-WinLine_Temp_Tables.sql
KammM 38d6a271c4 Anlage des Repos
2024-01-24 16:42:38 +01:00

981 lines
36 KiB
Transact-SQL
Raw Permalink Blame History

-- 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: 1.0.1.0
-- =================================================================
-- History:
DECLARE @Counter BIGINT = 0,
@TableSubString1 NVARCHAR(100),
@TableSubString2 NVARCHAR(100),
@DynamicSQL NVARCHAR(1000);
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 1 - Ansprechpartner'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'Ansprechpartner'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 1 - Ansprechpartner'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 2 - BELEGETEMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BELEGETEMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 2 - BELEGETEMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 3 - BELEGKOPFTEMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BELEGKOPFTEMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 3 - BELEGKOPFTEMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 4 - BELEGZEILETEMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BELEGZEILETEMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 4 - BELEGZEILETEMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 5 - BSTTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BSTTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 5 - BSTTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 6 - BSTTMPPARK'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BSTTMPPARK'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 6 - BSTTMPPARK'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 7 - BUCHTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BUCHTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 7 - BUCHTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 8 - BUDGETSELTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'BUDGETSELTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 8 - BUDGETSELTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 9 - FB3NK'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'FB3NK'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 9 - FB3NK'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 10 - FB3OPNR'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'FB3OPNR'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 10 - FB3OPNR'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 11 - G01W131'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G01W131'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 11 - G01W131'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 12 - G02W245COPY'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G02W245COPY'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 12 - G02W245COPY'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 13 - G02W245MCOPY'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G02W245MCOPY'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 13 - G02W245MCOPY'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 13 - G02W362K'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G02W362K'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 13 - G02W362K'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 13 - G02W362M'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G02W362M'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 13 - G02W362M'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 14 - G99W432X'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G99W432X'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 14 - G99W432X'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 14 - G99W458'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G99W458'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 14 - G99W458'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 14 - G99W458M'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G99W458M'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 14 - G99W458M'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 15 - G99W615DYN'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'G99W615DYN'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 15 - G99W615DYN'
PRINT '------------------------------------------------------------'
--------------------------------------------------------------------------------------------------------------------------------------------------------------
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 16 - LAGERBUCHTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 99999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'LAGERBUCHTMP'
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 4 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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 16 - LAGERBUCHTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 17 - LAGERBUCHTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 99999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'LAGERBUCHTMP'
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 4 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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 17 - LAGERBUCHTMP'
PRINT '------------------------------------------------------------'
--------------------------------------------------------------------------------------------------------------------------------------------------------------
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 18 - LAGERBUCHTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'LAGERBUCHTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 18 - LAGERBUCHTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 19 - LAGERKOMMTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'LAGERKOMMTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 19 - LAGERKOMMTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 20 - MESO530FAKTTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'MESO530FAKTTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 20 - MESO530FAKTTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 21 - PREISTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'PREISTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 21 - PREISTMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 22 - T025TMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'T025TMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 22 - T025TMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 23 - T026TMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'T026TMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 23 - T026TMP'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 24 - TMP432_'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'TMP432_'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 24 - TMP432_'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 25 - TMPANSPRECHPART_'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'TMPANSPRECHPART_'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 25 - TMPANSPRECHPART_'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 26 - TMPGRID432_'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'TMPGRID432_'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 26 - TMPGRID432_'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 27 - TMPGRID446_'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'TMPGRID446_'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 27 - TMPGRID446_'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 28 - TMPGRID458_'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'TMPGRID458_'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 28 - TMPGRID458_'
PRINT '------------------------------------------------------------'
PRINT '------------------------------------------------------------'
PRINT 'START Durchlauf 29 - VERTRETERBUCHTMP'
PRINT '------------------------------------------------------------'
WHILE @COUNTER < 999
BEGIN
SET @counter = @counter+1
SET @TableSubString1 = 'VERTRETERBUCHTMP'
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
PRINT ''
PRINT '------------------------------------------------------------'
PRINT 'ENDE Durchlauf 29 - VERTRETERBUCHTMP'
PRINT '------------------------------------------------------------'
View Git Blame Copy Permalink