dbo.QBM_PTypeDrop

1   create   procedure QBM_PTypeDrop (@typename nvarchar(64) ) as begin declare @SQLcmd nvarchar(max) declare @CurrentDeadlock_priority int declare
2 @DebugSwitch int = 0 declare @Statements table (ElementIndex int identity ,ContentFull nvarchar(max) collate database_default ) declare @ElementCount 
3int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY if not exists ( select top 1 1 from sys.types t where t.name = @Typename ) begin goto endlabel
4 end select top 1 @CurrentDeadlock_priority = s.deadlock_priority FROM sys.dm_exec_sessions s where s.session_id = @@spid select @CurrentDeadlock_priority
5 = isnull(@CurrentDeadlock_priority, 0) set deadlock_priority 8 insert into @Statements(ContentFull) select concat('drop ' , case x.objecttype when 'P'
6 then 'procedure' when 'TR' then 'trigger' when 'V' then 'view' when 'FN' then 'function' when 'TF' then 'function' when 'IF' then 'function' else 'unknown type'
7  end , ' "' , x.schemaname , N'"."' , x.objectname , N'"' )  from ( select o.name as objectname, s.name as schemaname, o.type as objecttype  from sys.parameter_type_usages
8 pu join sys.objects o on pu.object_id = o.object_id join sys.types t on pu.user_type_id = t.user_type_id join sys.schemas s on o.schema_id = s.schema_id
9 and s.name not in ( 'sys' , 'INFORMATION_SCHEMA' )  join sys.objects tt on tt.name like 'TT[_]' + t.name + '%' and tt.type = 'TT' join sys.columns tc 
10on tt.object_id = tc.object_id  where o.type in ( 'P', 'FN', 'TF', 'IF', 'V', 'TR') and t.name = @typename union   select td.name, s.name, td.type from
11 sys.objects td join sys.sql_modules m on td.object_id = m.object_id join sys.schemas s on td.schema_id = s.schema_id and s.name not in ( 'sys' , 'INFORMATION_SCHEMA'
12 ) where m.definition like concat('%', @typename , '%') and td.name <> isnull(object_name(@@procid),'')  ) as x select @ElementCount = @@ROWCOUNT select
13 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = s.ContentFull from @Statements s where s.ElementIndex = @ElementIndex
14 if @debugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd select @ElementIndex += 1 end  if exists (select top 1 1 from sys.types t where
15 t.name = @typename ) begin select @SQLcmd = 'drop type ' + @typename if @debugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd end END TRY
16 BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
17 END CATCH endLabel:  set deadlock_priority @CurrentDeadlock_priority return end 
18