Back to OIM Explorer

dbo.QBM_ZObjectKeyRepair

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.316 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_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FSQObjectKeyRepair_fn source text reference
  • references source dbo.QBM_FSQObjectKeyRepair_fn2 source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL97 lines
1CREATE PROCEDURE QBM_ZObjectKeyRepair(2  @SlotNumberDummy int,3  @TablePattern varchar(38),4  @Dummy2 varchar(38),5  @DummyGenProcID varchar(38)6)7AS8BEGIN9  DECLARE @TableName nvarchar(64)10  DECLARE @muster nvarchar(max)11  DECLARE @Ruf nvarchar(max)12  DECLARE @SQLcmd nvarchar(max)13  DECLARE @DebugSwitch int = 014  DECLARE @ElementBuffer QBM_YCursorBuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex int17  BEGIN TRY18    SELECT19      @muster = 'update %tabelle% 20	set XObjectKey = %ruf% #xwerte#21 from %tabelle% z with (readpast)22 where z.XObjectKey  <>  %ruf% collate  SQL_Latin1_General_CP1_CS_AS'23    INSERT INTO @ElementBuffer(Ident1)24    SELECT t.TableName25    FROM dialogcolumn c26      WITH(readpast)27    JOIN dialogtable t28      WITH(readpast)29      ON c.UID_DialogTable = t.UID_DialogTable AND t.tabletype IN('T',30    'V')31    JOIN information_schema.tables it32      WITH(readpast)33      ON t.TableName = it.table_name34    WHERE35      c.isPKMember = 1 AND EXISTS(36    SELECT TOP 1 137    FROM dialogcolumn cc38      WITH(readpast)39    WHERE40      cc.columnname = 'XObjectKey' AND cc.UID_DialogTable = c.UID_DialogTable) AND NOT EXISTS(41    SELECT TOP 1 142    FROM dialogtable tt43      WITH(readpast)44    WHERE45      tt.UID_DialogTableBase = c.UID_DialogTable) AND t.TableName LIKE @TablePattern AND t.TableName NOT IN(46    SELECT TableName47    FROM QBM_VHeavyLoadTables) AND t.TableName NOT LIKE '%[_]mem'48    GROUP BY t.TableName49    HAVING count(*) <= 250    ORDER BY 151    SELECT @ElementCount = @@ROWCOUNT52    SELECT @ElementIndex = 153    WHILE @ElementIndex <= @ElementCount54    BEGIN55      SELECT TOP 1 @TableName = bu.Ident156      FROM @ElementBuffer bu57      WHERE58        bu.ElementIndex = @ElementIndex59      SELECT60        @ruf = dbo.QBM_FSQObjectKeyRepair_fn2(@TableName,61        'z')62      SELECT63        @SQLcmd = replace(@muster,64        N '%ruf%',65        @ruf)66      SELECT67        @SQLcmd = replace(@SQLcmd,68        N '%tabelle%',69        @TableName)70      SELECT71        @SQLcmd = replace(@sqlcmd,72        '#xwerte#',73      CASE dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated')74        WHEN 1 THEN75      ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''76      ELSE ''77      END)78      IF @DebugSwitch > 079      BEGIN80        print @SQLcmd81      END82      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,83        @LockTimeout_ms = 3000,84        @MaxWaitTimeForLock_s = 15.0,85        @ProcIDForJournal = @@procid,86        @HandleErrorSilent = 087      SELECT @ElementIndex += 188    END89  END TRY90  BEGIN CATCH91    EXEC QBM_PSessionErrorAdd DEFAULT92    RAISERROR('',93    18,94    1)95      WITH NOWAIT96  END CATCH97END
Open raw exported source
SQL ยท Raw19 lines
1  create   procedure QBM_ZObjectKeyRepair ( @SlotNumberDummy int , @TablePattern varchar(38) , @Dummy2 varchar(38) , @DummyGenProcID varchar(38)2 ) as begin declare @TableName nvarchar(64) declare @muster nvarchar(max) declare @Ruf nvarchar(max) declare @SQLcmd nvarchar(max) declare @DebugSwitch3 int = 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int BEGIN TRY select @muster = 'update %tabelle% 4	set XObjectKey = %ruf% #xwerte#5 from %tabelle% z with (readpast)6 where z.XObjectKey  <>  %ruf% collate  SQL_Latin1_General_CP1_CS_AS'7   insert into @ElementBuffer (Ident1) select t.TableName  from dialogcolumn c with (readpast) join dialogtable t with (readpast) on c.UID_DialogTable 8= t.UID_DialogTable and t.tabletype in ('T', 'V')  join information_schema.tables it with (readpast) on t.TableName = it.table_name where c.isPKMember 9= 1 and exists (select top 1 1 from dialogcolumn cc with (readpast) where cc.columnname = 'XObjectKey' and cc.UID_DialogTable = c.UID_DialogTable ) and10 not exists (select top 1 1 from dialogtable tt with (readpast) where tt.UID_DialogTableBase = c.UID_DialogTable ) and t.TableName like @TablePattern and11 t.TableName not in (select TableName from QBM_VHeavyLoadTables  ) and t.TableName not like '%[_]mem' group by t.TableName  having count(*) <= 2 order 12by 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName = bu.Ident1 from @ElementBuffer13 bu where bu.ElementIndex = @ElementIndex select @ruf = dbo.QBM_FSQObjectKeyRepair_fn2 (@TableName, 'z' ) select @SQLcmd = replace(@muster, N'%ruf%', @ruf14) select @SQLcmd = replace(@SQLcmd, N'%tabelle%', @TableName ) select @SQLcmd = replace(@sqlcmd, '#xwerte#', case dbo.QBM_FGIColumnExistsInSchema(@TableName15, 'XDateUpdated') when 1 then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ) if @DebugSwitch > 0 begin16 print @SQLcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 3000 , @MaxWaitTimeForLock_s = 15.0 , @ProcIDForJournal17 = @@procid , @HandleErrorSilent = 0  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT18 END CATCH end 19