Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Action        : CleanUp Database'
Print ' Autor         : J.Feuerstein'
Print ' Creation Date : 21.01.2006'
Print ' Last Update   : 09.01.2014 / J.Fes Cleanup P V IF'
Print ' Last Update   : 06.03.2023 / J.Fes Optimice interations'
Print ' Last Update   : 09.09.2025 / J.Fes+Cris add @ObjSchema'
Print '-------------------------------------------------------------------------'

use [#DB_N#]
-- use [DB_JobManager_BSH_WorkInst_ProdSystem_SQL]
-- use [JobManagerV3_ABBSJ_WorkInst_DefaultSystem]
-- use [JobManagerV3_CustomerNameShort_WorkInst_DefaultSystem]

Print '-- [CleanUp Base] -------------------------------------------------'
Declare @ObjName nvarchar(4000)
Declare @ObjType nvarchar(4000)
Declare @ObjSchema nvarchar(4000)
Declare @ObjToDeleteExce nvarchar(4000)
Declare @InterationsMax integer

set @InterationsMax=0

While (SELECT TOP 1 Name FROM sysobjects
       WHERE type In ('P','V','IF','FN')
    order by type, name) <> ''
 begin

 SELECT Top(1) @ObjSchema = SCHEMA_NAME(schema_id), @ObjName=Name, @ObjType=type
 FROM sys.objects
 WHERE type IN ('P','V','IF','FN')
 ORDER BY type, name
  

   if @ObjType = 'P'
   begin
     Print '    |-- CleanUp: PROCEDURE.: [' + @ObjSchema + '].[' + @ObjName + ']'
     set @ObjToDeleteExce='Drop PROCEDURE [' + @ObjSchema + '].[' + @ObjName + ']'
     execute (@ObjToDeleteExce)
   end

   if @ObjType = 'V'
   begin
     Print '    |-- CleanUp: VIEW......: [' + @ObjSchema + '].[' + @ObjName + ']'
     set @ObjToDeleteExce='Drop View [' + @ObjSchema + '].[' + @ObjName + ']'
     execute (@ObjToDeleteExce)
   end

   if @ObjType = 'IF'
   begin
     Print '    |-- CleanUp: FUNCTION.IF: [' + @ObjSchema + '].[' + @ObjName + ']'
     set @ObjToDeleteExce='DROP FUNCTION [' + @ObjSchema + '].[' + @ObjName + ']'
     execute (@ObjToDeleteExce)
   end

   if @ObjType = 'FN'
   begin
     Print '    |-- CleanUp: FUNCTION.FN: [' + @ObjSchema + '].[' + @ObjName + ']'
     set @ObjToDeleteExce='DROP FUNCTION [' + @ObjSchema + '].[' + @ObjName + ']'
     execute (@ObjToDeleteExce)
   end


   set @InterationsMax = @InterationsMax + 1
   if @InterationsMax >= 10000 break
 end
GO



-- 21.01.2009 J.Fes CleanUp via Delete tempTables witch where used in ObjectsAndProcessData
If Exists (select Name from sysobjects where Name = 'zzzzzDebugTbl_Empty' and type = 'P')
begin
 print 'cleanup zzzzzDebugTbl via zzzzzDebugTbl_Empty'
 EXECUTE zzzzzDebugTbl_Empty
end

IF OBJECT_ID ('zztmpERG_tbl', N'U')  IS NOT NULL Drop table zztmpERG_tbl
IF OBJECT_ID ('zztmpMAX_tbl', N'U')  IS NOT NULL Drop table zztmpMAX_tbl
IF OBJECT_ID ('zztmpObj_tbl', N'U')  IS NOT NULL Drop table zztmpObj_tbl
IF OBJECT_ID ('zztmpObj2_tbl', N'U') IS NOT NULL Drop table zztmpObj2_tbl

if (object_id('tempdb..#zztmpERG_tbl') is not null)  Drop table #zztmpERG_tbl

If Exists (select Name from sysobjects where Name = 'ObjectsAndProcessData_View' and type = 'V')
 begin
    Print 'Drop: [ObjectsAndProcessData_View] 28.01.2009 J.Fes '
    Drop View ObjectsAndProcessData_View
 end
 
If Exists (select Name from sysobjects where Name = 'ObjectsDataSets_View' and type = 'V')
 begin
    Print 'Drop: [ObjectsDataSets_View] 28.01.2009 J.Fes --> ersetzt durch DS_with_Irs_View'
    Drop View ObjectsDataSets_View
 end

If Exists (select Name from sysobjects where Name = 'ObjectsIRs_View' and type = 'V')
 begin
    Print 'Drop: [ObjectsIRs_View] 28.01.2009 J.Fes '
    Drop View ObjectsIRs_View
 end

If Exists (select Name from sysobjects where Name = 'IrId_DsId_View' and type = 'V')
 begin
    Print 'Drop: [IrId_DsId_View] 28.01.2009 J.Fes '
    Drop View IrId_DsId_View
 end

If Exists (select Name
           from sysobjects
            where Name = 'DS_AndProcessData_View' and type = 'V')
 begin
  print 'Drop View DS_AndProcessData_View 2.3.09 J.Fes'
  Drop View DS_AndProcessData_View
 end
-----------------------------------------------------------------------------
If Exists (select Name
           from sysobjects
            where Name = 'IRs_AndProcessData_View' and type = 'V')
 begin
  print 'Drop View IRs_AndProcessData_View 2.3.09 J.Fes'
  Drop View IRs_AndProcessData_View
 end

GO

EndeExit:
 Print '------------------------------------------------------------------------'
 Print ('CleanUp Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'
GO