dbo.QBM_PProcedureDrop

1 create   procedure QBM_PProcedureDrop (@pattern nvarchar(255) , @force bit = 0  )  as begin  declare @SQLcmd nvarchar(max) declare @name nvarchar
2(255)  declare @LengthMessage nvarchar(256) = '#LDS#Name of Element {0} is too long (30).|' declare @ElementBuffer     table (ElementIndex int identity
3 , Int1 int default 0 , ContentShort nvarchar(400) collate database_default , LongIdent1 nvarchar(256) collate database_default , LongIdent2 nvarchar(256
4) collate database_default , ContentFull nvarchar(max) collate database_default ) declare @ElementCount int declare @ElementIndex int declare @crlf nvarchar
5(16) = nchar(13) + nchar(10) declare @CommentCode bit = 0 declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY if @pattern not like '%[%]%' and @force
6 = 0 and exists ( select top 1 1 from sys.objects o where o.name = @pattern and o.type in( 'P'  ) ) begin select @SQLcmd = CONCAT('drop procedure if exists '
7, @pattern) exec sp_executesql @SQLCmd  goto verarbeitet end if @@NESTLEVEL < 2  begin if exists (select top 1 1 from sys.tables t where t.name = 'QBMModuleDef'
8 ) begin if exists (select top 1 1 from QBMModuleDef d where d.UID_ModuleDef = 'MDK-Moduledefinition' ) and exists (select top 1 1 from information_schema.routines
9 r where r.ROUTINE_type = N'function' and r.ROUTINE_NAME = 'QBM_FSQObjectComment' ) begin select @CommentCode = 1 end end end insert into @ElementBuffer
10(LongIdent1, LongIdent2, ContentFull) select r.ROUTINE_NAME , r.ROUTINE_SCHEMA, concat( case @CommentCode when 1 then concat('print dbo.QBM_FSQObjectComment ('''
11 , r.ROUTINE_NAME , ''')' , @crlf ) else '' end , 'drop procedure if exists "' , r.ROUTINE_SCHEMA, N'"."' , r.ROUTINE_NAME , N'"' )  from information_schema.routines
12 r join sys.schemas s on r.ROUTINE_SCHEMA = s.name collate database_default where r.ROUTINE_type = N'procedure' collate database_default and dbo.QBM_FGIObjectIsDropable
13(s.schema_id) = 1 and r.ROUTINE_NAME like @pattern collate database_default and r.ROUTINE_NAME not in ( N'QBM_PProcedureDrop' collate database_default 
14  , N'QBM_PSessionErrorAdd' , N'QBM_PSessionContextSet' ) select @ElementCount = @@ROWCOUNT if exists (select top 1 1 from @ElementBuffer e where len(e.LongIdent1
15) > 30 and @force = 0 ) begin select top 1 @name = e.LongIdent1 from @ElementBuffer e where len(e.LongIdent1) > 30 select @LengthMessage = concat(@LengthMessage
16 , @name , '|') raiserror(@LengthMessage, 18, 1) with nowait end select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd
17 = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd select
18 @ElementIndex += 1 end verarbeitet: END TRY BEGIN CATCH declare @Message nvarchar(4000)  if exists (select top 1 1 from sys.objects o where o.name = 'QBM_PSessionErrorAdd'
19 ) begin exec QBM_PSessionErrorAdd default, @SQLcmd RAISERROR ('', 18, 1) WITH NOWAIT end else begin  select @Message = isnull(ERROR_MESSAGE() , '<no message>'
20) RAISERROR (@Message, 18, 1) WITH NOWAIT end END CATCH end 
21