Back to OIM Explorer

dbo.QBM_PConstraintDisable

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.354 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_FCVIntToString source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PIndicatorChange source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL100 lines
1CREATE PROCEDURE QBM_PConstraintDisable(2  @TableName nvarchar(64) = N '%'3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(max)7  DECLARE @TabAktuell nvarchar(64)8  DECLARE @elements QBM_YCursorBuffer9  DECLARE @ElementCount int10  DECLARE @ElementIndex int11  DECLARE @JournalMessage nvarchar(1000)12  DECLARE @DebugLevel varchar(1) = 'I'13  DECLARE @MessageType varchar(1) = 'W'14  DECLARE @CountElementsChanged int = 015  DECLARE @SQL nvarchar(max)16  SET XACT_ABORT OFF17  BEGIN TRY18    INSERT INTO @elements(Ident2)19    SELECT20      DISTINCT c.name AS TableName21    FROM sys.foreign_keys fk22    JOIN sys.objects c23      ON c.object_id = fk.parent_object_id24    JOIN sys.objects p25      ON p.object_id = fk.referenced_object_id26    WHERE27      fk.type = 'F' AND(c.name LIKE @TableName) AND(convert(int, fk.is_disabled)) = 028    SELECT @ElementCount = @@ROWCOUNT29    IF @ElementCount > 030    BEGIN31      EXEC QBM_PIndicatorChange 032    END33    SELECT @ElementIndex = 134    WHILE @ElementIndex <= @ElementCount35    BEGIN36      SELECT TOP 1 @TabAktuell = bu.Ident237      FROM @elements bu38      WHERE39        bu.ElementIndex = @ElementIndex40      SELECT41        @SQL = CONCAT('42			alter Table ',43        @TabAktuell,44        ' nocheck constraint all ')45      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQL,46        @LockTimeout_ms = 2500,47        @MaxWaitTimeForLock_s = 10,48        @ProcIDForJournal = DEFAULT,49        @HandleErrorSilent = 0,50        @ExecuteWithTransact = 051      SELECT @CountElementsChanged += 152      SELECT @ElementIndex += 153    END54    IF @CountElementsChanged > 0 AND EXISTS(55      SELECT TOP 1 156      FROM sys.objects o57      WHERE58        o.name = 'QBM_PJournal' AND type = 'P')59    BEGIN60      SELECT61        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),62        '') + '|' + @TableName + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'63      IF NOT EXISTS(64        SELECT TOP 1 165        FROM DialogDatabase66        WHERE67          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(68      SELECT TOP 1 169      FROM sys.tables t70        WITH(readpast)71      JOIN sys.columns c72        WITH(readpast)73        ON t.object_id = c.object_id74      WHERE75        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')76      BEGIN77        EXEC QBM_PJournal @JournalMessage,78          @@procid,79          @MessageType,80          @DebugLevel,81        2082      END83      ELSE84      BEGIN85        EXEC QBM_PJournal @JournalMessage,86          @@procid,87        'I',88        'I'89      END90    END91  END TRY92  BEGIN CATCH93    EXEC QBM_PSessionErrorAdd DEFAULT94    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()95    RAISERROR(@Rethrow,96    18,97    1)98      WITH NOWAIT99  END CATCH100END
Open raw exported source
SQL ยท Raw17 lines
1  create   procedure QBM_PConstraintDisable (@TableName nvarchar(64)= N'%' ) as begin declare @SQLcmd nvarchar(max) declare @TabAktuell nvarchar2(64)     declare @elements QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @JournalMessage nvarchar(1000) declare @DebugLevel3 varchar(1) = 'I' declare @MessageType varchar(1) = 'W' declare @CountElementsChanged int = 0 declare @SQL nvarchar(max) SET XACT_ABORT OFF BEGIN TRY insert4 into @elements (Ident2) select distinct c.name as TableName  from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id join sys.objects5 p on p.object_id = fk.referenced_object_id where fk.type = 'F' and (c.name like @TableName  ) and (convert(int, fk.is_disabled)) = 0 select @ElementCount6 = @@ROWCOUNT  if @ElementCount > 0 begin exec QBM_PIndicatorChange 0 end select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin  select top7 1 @TabAktuell = bu.Ident2 from @elements bu where bu.ElementIndex = @ElementIndex select @SQL = concat('8			alter Table ', @TabAktuell, ' nocheck constraint all '9) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQL , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10 , @ProcIDForJournal = default , @HandleErrorSilent10 = 0   , @ExecuteWithTransact = 0 select @CountElementsChanged += 1 select @ElementIndex += 1 end  if @CountElementsChanged > 0 and exists (select top 111 1 from sys.objects o where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|'12 + isnull(OBJECT_NAME(@@procid), '') + '|' + @TableName + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'  if not exists (select top 1 1 from13 DialogDatabase where IsMainDatabase = 1 and UpdatePhase <> 0) and not exists (select top 1 1 from sys.tables t with (readpast) join sys.columns c with14 (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage15, @@procid, @MessageType, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd16 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 17