Back to OIM Explorer

dbo.QBM_PTypeDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.585 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_PModuleRemove

Complete Source

1CREATE PROCEDURE QBM_PTypeDrop(2  @typename nvarchar(64)3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(max)7  DECLARE @CurrentDeadlock_priority int8  DECLARE @DebugSwitch int = 09  DECLARE @Statements TABLE(ElementIndex int identity,10  ContentFull nvarchar(max) collate database_default)11  DECLARE @ElementCount int12  DECLARE @ElementIndex int13  SET XACT_ABORT OFF14  BEGIN TRY15    IF NOT EXISTS(16      SELECT TOP 1 117      FROM sys.types t18      WHERE19        t.name = @Typename)20    BEGIN21      GOTO endlabel22    END23    SELECT TOP 1 @CurrentDeadlock_priority = s.deadlock_priority24    FROM sys.dm_exec_sessions s25    WHERE26      s.session_id = @@spid27    SELECT28      @CurrentDeadlock_priority = isnull(@CurrentDeadlock_priority,29      0)30    SET deadlock_priority 831    INSERT INTO @Statements(ContentFull)32    SELECT33      CONCAT('drop ',34    CASE x.objecttype35      WHEN 'P' THEN36    'procedure'37      WHEN 'TR' THEN38    'trigger'39      WHEN 'V' THEN40    'view'41      WHEN 'FN' THEN42    'function'43      WHEN 'TF' THEN44    'function'45      WHEN 'IF' THEN46    'function'47    ELSE 'unknown type'48    END,49    ' "',50    x.schemaname,51    N '"."',52    x.objectname,53    N '"')54    FROM(55    SELECT56      o.name AS objectname,57      s.name AS schemaname,58      o.type AS objecttype59    FROM sys.parameter_type_usages pu60    JOIN sys.objects o61      ON pu.object_id = o.object_id62    JOIN sys.types t63      ON pu.user_type_id = t.user_type_id64    JOIN sys.schemas s65      ON o.schema_id = s.schema_id AND s.name NOT IN('sys', 'INFORMATION_SCHEMA')66    JOIN sys.objects tt67      ON tt.name LIKE 'TT[_]' + t.name + '%' AND tt.type = 'TT'68    JOIN sys.columns tc69      ON tt.object_id = tc.object_id70    WHERE71      o.type IN('P', 'FN', 'TF', 'IF', 'V', 'TR') AND t.name = @typename72    UNION73    SELECT74      td.name,75      s.name,76      td.type77    FROM sys.objects td78    JOIN sys.sql_modules m79      ON td.object_id = m.object_id80    JOIN sys.schemas s81      ON td.schema_id = s.schema_id AND s.name NOT IN('sys', 'INFORMATION_SCHEMA')82    WHERE83      m.definition LIKE CONCAT('%', @typename, '%') AND td.name <> isnull(object_name(@@procid), '')) AS x84    SELECT @ElementCount = @@ROWCOUNT85    SELECT @ElementIndex = 186    WHILE @ElementIndex <= @ElementCount87    BEGIN88      SELECT TOP 1 @SQLcmd = s.ContentFull89      FROM @Statements s90      WHERE91        s.ElementIndex = @ElementIndex92      IF @debugSwitch > 093      BEGIN94        print @sqlcmd95      END96      EXEC sp_executeSQL @SQLcmd97      SELECT @ElementIndex += 198    END99    IF EXISTS(100      SELECT TOP 1 1101      FROM sys.types t102      WHERE103        t.name = @typename)104    BEGIN105      SELECT @SQLcmd = 'drop type ' + @typename106      IF @debugSwitch > 0107      BEGIN108        print @sqlcmd109      END110      EXEC sp_executeSQL @SQLcmd111    END112  END TRY113  BEGIN CATCH114    EXEC QBM_PSessionErrorAdd DEFAULT115    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()116    RAISERROR(@Rethrow,117    18,118    1)119      WITH NOWAIT120  END CATCH121  endLabel:122  SET deadlock_priority @CurrentDeadlock_priority123  RETURN124END

Open raw exported source

1   create   procedure QBM_PTypeDrop (@typename nvarchar(64) ) as begin declare @SQLcmd nvarchar(max) declare @CurrentDeadlock_priority int declare2 @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 endlabel4 end select top 1 @CurrentDeadlock_priority = s.deadlock_priority FROM sys.dm_exec_sessions s where s.session_id = @@spid select @CurrentDeadlock_priority5 = 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_usages8 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_id9 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 from11 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 select13 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = s.ContentFull from @Statements s where s.ElementIndex = @ElementIndex14 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 where15 t.name = @typename ) begin select @SQLcmd = 'drop type ' + @typename if @debugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd end END TRY16 BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT17 END CATCH endLabel:  set deadlock_priority @CurrentDeadlock_priority return end 18