dbo.QBM_PFunctionDrop

1  create   procedure QBM_PFunctionDrop (@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( 'FN'  , 'IF'  , 'TF'  ) ) begin select @SQLcmd = CONCAT('drop function if exists dbo.'
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 function 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'function' 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 ('QBM_FGIObjectIsDropable', 'QBM_FSQObjectComment'
14 collate database_default) select @ElementCount = @@ROWCOUNT if exists (select top 1 1 from @ElementBuffer e where len(e.LongIdent1) > 30 and @force = 
150 ) begin select top 1 @name = e.LongIdent1 from @ElementBuffer e where len(e.LongIdent1) > 30 select @LengthMessage = concat(@LengthMessage , @name , 
16'|') raiserror(@LengthMessage, 18, 1) with nowait end    select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull
17 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd select @ElementIndex
18 += 1 end verarbeitet: END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
19