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 '-------------------------------------------------------------------------'
use [#DB_N#]


Print '-- [CleanUp Base] -------------------------------------------------'
Declare @ObjName nvarchar(4000)
Declare @ObjType 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')) <> ""
 begin
  select Top(1) @ObjName=Name, @ObjType=type  from sysobjects WHERE type In ('P','V','IF') order by type

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

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

   if @ObjType = 'IF'
   begin
     Print '    |-- CleanUp: FUNCTION.: [' + @ObjName + ']'
     set @ObjToDeleteExce="DROP FUNCTION " + @ObjName
     execute (@ObjToDeleteExce)
   end
   set @InterationsMax = @InterationsMax + 1
   if @InterationsMax >= 100 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
