Back to OIM Explorer

dbo.QBM_PTableDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.020 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_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PConstraintFKDrop source text reference
  • references source dbo.QBM_PSchemaBindDisable source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL188 lines
1CREATE PROCEDURE QBM_PTableDrop(2  @TableName nvarchar(64),3  @force BIT = 04)5AS6BEGIN7  DECLARE @SQLcmd nvarchar(1024)8  DECLARE @Message nvarchar(1000)9  DECLARE @uid_DialogTable varchar(38)10  DECLARE @QBM_VQBMRelationExists BIT = 011  DECLARE @QBM_VQBMRelationALLExists BIT = 012  DECLARE @DebugSwitch int = 013  DECLARE @ChildCmd QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  SET XACT_ABORT OFF17  BEGIN TRY18    EXEC QBM_PSchemaBindDisable @TableName19    DECLARE @childtable varchar(64)20    IF EXISTS(21      SELECT TOP 1 122      FROM sys.objects t23      WHERE24        t.name = @TableName AND t.type <> 'U')25    BEGIN26      SELECT27        @Message = '#LDS#Cannot delete Object {0} because it is not found as a table {1}.|' + @TableName + '|'28      RAISERROR(@Message,29      18,30      1)31        WITH nowait32    END33    IF EXISTS(34      SELECT TOP 1 135      FROM sys.objects o36      WHERE37        o.name = 'QBM_VQBMRelationALL')38    BEGIN39      SELECT @QBM_VQBMRelationALLExists = 140    END41    IF EXISTS(42      SELECT TOP 1 143      FROM sys.objects o44      WHERE45        o.name = 'QBM_VQBMRelation')46    BEGIN47      SELECT @QBM_VQBMRelationExists = 148    END49    IF @QBM_VQBMRelationALLExists = 150    BEGIN51      SELECT52        @SQLcmd = CONCAT('53						select top 1 154							from QBM_VQBMRelationALL r55							where r.ParentTable = ''',56        @TableName,57        '''58							and r.ParentTable <> r.ChildTable')59      EXEC sp_executesql @SQLCmd60      IF @@ROWCOUNT > 061      BEGIN62        SELECT63          @Message = '#LDS#Cannot delete Table {0} because it is referenced by table {1}.|' + @TableName + '|' + @childtable + '|'64        RAISERROR(@Message,65        18,66        1)67          WITH nowait68      END69    END70    SELECT TOP 1 @uid_DialogTable = t.UID_DialogTable71    FROM DialogTable t72    WHERE73      t.TableName = @TableName74    IF @uid_DialogTable IS NULL AND @force = 075    BEGIN76      SELECT77        @Message = '#LDS#Cannot delete Table {0} because it does not exist in DialogTable.|' + @TableName + '|'78      RAISERROR(@Message,79      18,80      1)81        WITH nowait82    END83    IF dbo.QBM_FCVGUIDToModuleOwner(@uid_DialogTable) <> dbo.QBM_FGIDBOwner() AND @force = 084    BEGIN85      SELECT86        @Message = '#LDS#Cannot delete Table {0} because it is not under Your ownership.|' + @TableName + '|'87      RAISERROR(@Message,88      18,89      1)90        WITH nowait91    END92    EXEC QBM_PConstraintFKDrop @TableName,93    '%',94    '%'95    INSERT INTO @ChildCmd(ContentFull)96    SELECT97      DISTINCT CONCAT('delete ',98      r.ChildTable,99      '100			from ',101      r.ChildTable,102      ' c 103			where c.',104      r.ChildColumn,105      ' = ''',106      @UID_DialogTable,107      '''108			')109    FROM QBM_VQBMRelation r110    JOIN sys.tables t111      ON r.ChildTable = t.name112    JOIN sys.columns c113      ON t.object_id = c.object_id AND c.name = r.ChildColumn114    WHERE115      ParentTable = 'dialogtable'116    SELECT @ElementCount = @@rowcount117    BEGIN118      TRANSACTION119      IF @QBM_VQBMRelationExists = 1120      BEGIN121        SELECT122          @SQLcmd = '123		delete QBMRelation 124		--select r.RelationID125			from QBMRelation r join  QBM_VQBMRelation v on r.UID_QBMRelation = v.UID_QBMRelation126			where v.ChildTable = '''127          + @TableName + ''''128        EXEC sp_executesql @SQLcmd129      END130      IF @QBM_VQBMRelationALLExists = 1131      BEGIN132        SELECT133          @SQLcmd = '134			delete DialogValidDynamicRef135				from DialogValidDynamicRef r join  QBM_VQBMRelationALL v on r.XObjectKey = v.XObjectKey136				where v.ChildTable = '''137          + @TableName + ''''138        EXEC sp_executesql @SQLcmd139      END140      DELETE DialogColumnGroupRight141      FROM DialogColumnGroupRight gr142      JOIN DialogColumn c143        ON gr.UID_DialogColumn = c.UID_DialogColumn144      WHERE145        c.UID_DialogTable = @UID_DialogTable146      DELETE DialogColumn147      FROM DialogColumn c148      WHERE149        c.UID_DialogTable = @UID_DialogTable150      SELECT @ElementIndex = 1151      WHILE @ElementIndex <= @ElementCount152      BEGIN153        SELECT TOP 1 @SQLCmd = bu.ContentFull154        FROM @ChildCmd bu155        WHERE156          bu.ElementIndex = @ElementIndex157        EXEC sp_executesql @sqlcmd158        SELECT @ElementIndex += 1159      END160      DELETE DialogTable161      WHERE162        UID_DialogTable = @uid_DialogTable163      IF EXISTS(164        SELECT TOP 1 1165        FROM sys.tables t166        WHERE167          t.name = @TableName)168      BEGIN169        SELECT @SQLcmd = 'Drop table ' + @TableName170        IF @DebugSwitch > 0171        BEGIN172          print @SQLcmd173        END174        EXEC sp_executesql @SQLcmd175      END176      COMMIT TRANSACTION177    END TRY178    BEGIN CATCH179      EXEC QBM_PSessionErrorAdd DEFAULT ROLLBACK TRANSACTION180      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()181      RAISERROR(@Rethrow,182      18,183      1)184        WITH NOWAIT185    END CATCH186    endLabel:187    RETURN188  END
Open raw exported source
SQL ยท Raw39 lines
1   create   procedure QBM_PTableDrop ( @TableName nvarchar(64) , @force bit = 0 ) as begin declare @SQLcmd nvarchar(1024) declare @Message nvarchar2(1000) declare @uid_DialogTable varchar(38) declare @QBM_VQBMRelationExists bit = 0 declare @QBM_VQBMRelationALLExists bit = 0 declare @DebugSwitch int3 = 0  declare @ChildCmd QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY exec QBM_PSchemaBindDisable 4@TableName declare @childtable varchar(64) if exists (select top 1 1 from sys.objects t where t.name = @TableName and t.type <> 'U' ) begin select @Message5 = '#LDS#Cannot delete Object {0} because it is not found as a table {1}.|' + @TableName + '|' raiserror (@Message, 18, 1) with nowait end if exists (select6 top 1 1 from sys.objects o where o.name = 'QBM_VQBMRelationALL' ) begin select @QBM_VQBMRelationALLExists = 1 end if exists (select top 1 1 from sys.objects7 o where o.name = 'QBM_VQBMRelation' ) begin select @QBM_VQBMRelationExists = 1 end if @QBM_VQBMRelationALLExists = 1 begin select @SQLcmd = concat( '8						select top 1 19							from QBM_VQBMRelationALL r10							where r.ParentTable = '''11, @TableName , '''12							and r.ParentTable <> r.ChildTable' ) exec sp_executesql @SQLCmd if @@ROWCOUNT > 0 begin select @Message = '#LDS#Cannot delete Table {0} because it is referenced by table {1}.|'13 + @TableName + '|' + @childtable + '|' raiserror (@Message, 18, 1) with nowait end end select top 1 @uid_DialogTable = t.UID_DialogTable from DialogTable14 t where t.TableName = @TableName if @uid_DialogTable is null and @force = 0 begin select @Message = '#LDS#Cannot delete Table {0} because it does not exist in DialogTable.|'15 + @TableName + '|' raiserror (@Message, 18, 1) with nowait end if dbo.QBM_FCVGUIDToModuleOwner(@uid_DialogTable) <> dbo.QBM_FGIDBOwner() and @force = 160 begin select @Message = '#LDS#Cannot delete Table {0} because it is not under Your ownership.|' + @TableName + '|' raiserror (@Message, 18, 1) with nowait17 end exec QBM_PConstraintFKDrop @TableName, '%', '%'   insert into @ChildCmd(ContentFull) select distinct concat( 'delete ' , r.ChildTable , '18			from '19 , r.ChildTable , ' c 20			where c.' , r.ChildColumn , ' = ''', @UID_DialogTable, '''21			' ) from QBM_VQBMRelation r join sys.tables t on r.ChildTable22 = t.name join sys.columns c on t.object_id = c.object_id and c.name = r.ChildColumn where ParentTable = 'dialogtable' select @ElementCount = @@rowcount23 begin transaction if @QBM_VQBMRelationExists = 1 begin select @SQLcmd = '24		delete QBMRelation 25		--select r.RelationID26			from QBMRelation r join  QBM_VQBMRelation v on r.UID_QBMRelation = v.UID_QBMRelation27			where v.ChildTable = '''28 + @TableName + '''' exec sp_executesql @SQLcmd end if @QBM_VQBMRelationALLExists = 1 begin select @SQLcmd = '29			delete DialogValidDynamicRef30				from DialogValidDynamicRef r join  QBM_VQBMRelationALL v on r.XObjectKey = v.XObjectKey31				where v.ChildTable = '''32 + @TableName + '''' exec sp_executesql @SQLcmd end   delete DialogColumnGroupRight from DialogColumnGroupRight gr join DialogColumn c on gr.UID_DialogColumn33 = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable delete DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTable  select34 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ChildCmd bu where bu.ElementIndex = @ElementIndex35 exec sp_executesql @sqlcmd select @ElementIndex += 1 end  delete DialogTable where UID_DialogTable = @uid_DialogTable if exists (select top 1 1 from sys.tables36 t where t.name = @TableName ) begin select @SQLcmd = 'Drop table ' + @TableName if @DebugSwitch > 0 begin print @SQLcmd end exec sp_executesql @SQLcmd37 end commit transaction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transaction declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow38() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 39