Back to OIM Explorer

dbo.QBM_PCustomSQLDependencyFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.733 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_FSQRemoveComment source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference
  • references source dbo.QBM_ZCustomSQLFill source text reference

References

Referenced By

Complete Source

SQL322 lines
1CREATE PROCEDURE QBM_PCustomSQLDependencyFill2AS3BEGIN4  DECLARE @Scriptname nvarchar(255)5  DECLARE @SQLOrig nvarchar(max)6  DECLARE @SQLRepl nvarchar(max)7  DECLARE @Posi int8  DECLARE @merken TABLE(UID_QBMCustomSQL varchar(38) collate database_default,9  scriptname nvarchar(255) collate database_default,10  countDeps int,11  SortOrder int,12  ScriptCode nvarchar(max) collate database_default,13  primary key(UID_QBMCustomSQL))14  DECLARE @work TABLE(UID_QBMCustomSQL varchar(38) collate database_default,15  scriptname nvarchar(255) collate database_default,16  countDeps int,17  SortOrder int,18  ScriptCode nvarchar(max) collate database_default,19  primary key(UID_QBMCustomSQL))20  DECLARE @lauf int21  DECLARE @AnzahlObjekte int22  DECLARE @CountDeps_neu int23  DECLARE @Sortorder_neu int24  DECLARE @Geaendert int25  DECLARE @Schema_Id int26  DECLARE @AllesOK int27  DECLARE @UID_QBMCustomSQL varchar(38)28  DECLARE @DebugSwitch int = 029  DECLARE @CountItems INT30  DECLARE @XUser nvarchar(64) = object_name(@@procid)31  DECLARE @Xdate datetime = getutcdate()32  SET XACT_ABORT OFF33  BEGIN TRY34    SELECT @AllesOK = 135    SELECT TOP 1 @Schema_Id = schema_id36    FROM sys.schemas37    WHERE38      name = N 'dbo'39    IF @DebugSwitch > 040    BEGIN41      print 'Start ' + convert(varchar(64),42      getutcdate(),43      121)44    END45    INSERT INTO @merken(UID_QBMCustomSQL,46    scriptname,47    CountDeps,48    SortOrder)49    SELECT50      max(cs.uid_QBMCustomSQL),51      cs.scriptname,52      isnull(count(DISTINCT v.object_id),53      0),54      155    FROM QBMCustomSQL cs56    JOIN sys.objects s57      ON cs.scriptname = s.name collate database_default58    LEFT59    OUTER60    JOIN sys.sql_dependencies d61      ON s.object_id = d.object_id62    LEFT63    OUTER64    JOIN sys.objects v65      ON d.referenced_major_id = v.object_id66    LEFT67    OUTER68    JOIN QBMCustomSQL vs69      ON v.name = vs.Scriptname collate database_default AND v.schema_id = @Schema_Id70    WHERE71      cs.scriptname <> isnull(vs.scriptname,72    N '') AND s.schema_id = @Schema_Id AND cs.xtouched <> 'F' AND cs.scripttype IN('P',73    'F',74    'V',75    'T',76    'N') AND cs.scriptname NOT IN('QBM_ZCustomSQLFill',77    OBJECT_NAME(@@procid))78    GROUP BY cs.scriptname79    SELECT @AnzahlObjekte = count(*)80    FROM @merken81    IF @DebugSwitch > 082    BEGIN83      print '@merken gefüllt ' + convert(varchar(64),84      getutcdate(),85      121)86    END87    UPDATE @merken88    SET ScriptCode = dbo.QBM_FSQRemoveComment(sc.Scriptcode)89    FROM @merken m90    JOIN qbmCustomsql sc91      ON sc.UID_QBMCustomSQL = m.UID_QBMCustomSQL92    UPDATE @merken93    SET SortOrder = patindex(N '%create[ ' + nchar(9) + N ']%',94    Scriptcode)95    IF @DebugSwitch > 096    BEGIN97      print '@merken dekommentiert ' + convert(varchar(64),98      getutcdate(),99      121)100    END101    DELETE @merken102    WHERE103      isnull(SortOrder,104    0) = 0105    SELECT @CountItems = @@ROWCOUNT106    IF @DebugSwitch > 0107    BEGIN108      print 'sätze ohne create gelöscht ' + str(@CountItems)109    END110    UPDATE @merken111    SET ScriptCode = CASE112    WHEN SortOrder = 1 THEN113    N 'alter' + substring(ScriptCode,114    7,115    1000000)116    ELSE substring(ScriptCode,117    1,118    SortOrder -1) + N 'alter' + substring(ScriptCode,119    @posi+6,120    1000000)121    END122    UPDATE @merken123    SET SortOrder = 1124    IF @DebugSwitch > 0125    BEGIN126      print '@merken mit alter ' + convert(varchar(64),127      getutcdate(),128      121)129    END130    SELECT @lauf = 1131    WHILE @lauf <= @AnzahlObjekte * 2 AND @AllesOK = 1132    BEGIN133      SELECT @geaendert = 0134      IF @DebugSwitch > 0135      BEGIN136        print '####################### neuer Durchlauf ' +str(@lauf) + str(@AnzahlObjekte)137      END138      DELETE @work139      INSERT @work(ScriptCode,140      SortOrder,141      UID_QBMCustomSQL,142      countDeps,143      scriptname)144      SELECT145        ScriptCode,146        SortOrder,147        UID_QBMCustomSQL,148        countDeps,149        scriptname150      FROM @merken m151      WHILE EXISTS(152      SELECT TOP 1 1153      FROM @work) AND @AllesOK = 1154      BEGIN155        SELECT156          TOP 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL,157          @Scriptname = w.scriptname,158          @SQLOrig = w.ScriptCode159        FROM @work w160        ORDER BY w.SortOrder,161        w.scriptname DESC162        IF @DebugSwitch > 0163        BEGIN164          print @Scriptname165        END166        BEGIN TRY167          BEGIN TRY168            EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLRepl,169              @LockTimeout_ms = 50,170              @MaxWaitTimeForLock_s = 2,171              @ProcIDForJournal = @@procid,172              @HandleErrorSilent = 0173            EXEC QBM_PSessionErrorClean174          END TRY175          BEGIN CATCH176            IF @DebugSwitch > 0177            BEGIN178              print @scriptname print isnull(ERROR_MESSAGE(),179              '<no message>')180            END181            UPDATE qbmCustomSQL182            SET XTouched = 'F',183            XDateUpdated = @Xdate,184            XUserUpdated = @XUser185            WHERE186              UID_QBMCustomSQL = @UID_QBMCustomSQL187            DELETE @merken188            WHERE189              UID_QBMCustomSQL = @UID_QBMCustomSQL CONTINUE190          END CATCH191        END TRY192        BEGIN CATCH193          SELECT @AllesOK = 0194        END CATCH195        IF @AllesOK = 0196        BEGIN197          CONTINUE198        END199        SELECT @CountDeps_neu = NULL200        SELECT201          @CountDeps_neu = isnull(count(DISTINCT v.object_id),202          0)203        FROM sys.objects s204        LEFT205        OUTER206        JOIN sys.sql_dependencies d207          ON s.object_id = d.object_id208        LEFT209        OUTER210        JOIN sys.objects v211          ON d.referenced_major_id = v.object_id212        LEFT213        OUTER214        JOIN QBMCustomSQL vs215          ON v.name = vs.Scriptname collate database_default216        WHERE217          s.name = @Scriptname AND vs.UID_QBMCustomSQL <> @UID_QBMCustomSQL AND s.schema_id = @Schema_Id AND v.schema_id = @Schema_Id218        GROUP BY s.object_id219        SELECT220          @CountDeps_neu = isnull(@CountDeps_neu,221          0)222        SELECT @Sortorder_neu = NULL223        SELECT224          @Sortorder_neu = isnull(max(vm.SortOrder),225          0) + 1226        FROM sys.objects s227        LEFT228        OUTER229        JOIN sys.sql_dependencies d230          ON s.object_id = d.object_id231        LEFT232        OUTER233        JOIN sys.objects v234          ON d.referenced_major_id = v.object_id235        LEFT236        OUTER237        JOIN QBMCustomSQL vs238          ON v.name = vs.Scriptname collate database_default239        LEFT240        OUTER241        JOIN @merken vm242          ON vs.UID_QBMCustomSQL = vm.UID_QBMCustomSQL collate database_default243        WHERE244          s.name = @Scriptname AND vm.scriptname <> @Scriptname AND s.schema_id = @Schema_Id AND v.schema_id = @Schema_Id245        GROUP BY s.name246        SELECT247          @Sortorder_neu = isnull(@Sortorder_neu,248          1)249        IF @DebugSwitch > 0250        BEGIN251          print @scriptname + str(@CountDeps_neu) + str(@Sortorder_neu)252        END253        UPDATE @merken254        SET countDeps = @CountDeps_neu255        WHERE256          UID_QBMCustomSQL = @UID_QBMCustomSQL AND countDeps <> @CountDeps_neu257        IF @@rowcount > 0258        BEGIN259          IF @DebugSwitch > 0260          BEGIN261            print 'count deps geändert'262          END263          SELECT @geaendert = 1264        END265        UPDATE @merken266        SET SortOrder = @Sortorder_neu267        WHERE268          UID_QBMCustomSQL = @UID_QBMCustomSQL AND SortOrder < @Sortorder_neu269        IF @@rowcount > 0270        BEGIN271          IF @DebugSwitch > 0272          BEGIN273            print 'SortOrder  geändert'274          END275          SELECT @geaendert = 1276        END277        DELETE @work278        WHERE279          UID_QBMCustomSQL = @UID_QBMCustomSQL280      END281      IF @AllesOK = 0282      BEGIN283        CONTINUE284      END285      IF @geaendert = 1286      BEGIN287        SELECT @lauf = @lauf +1288      END289      ELSE290      BEGIN291        SELECT @lauf = @AnzahlObjekte * 2 +1292      END293    END294    UPDATE QBMCustomSQl295    SET SortOrder = m.SortOrder,296    XDateUpdated = @Xdate,297    XUserUpdated = @XUser298    FROM QBMCustomSQl cu299    JOIN @merken m300      ON cu.UID_QBMCustomSQl = m.UID_QBMCustomSQL301    WHERE302      m.SortOrder <> ISNULL(cu.SortOrder,303    0)304    IF @DebugSwitch > 0305    BEGIN306      SELECT307        s.SortOrder,308        s.ScriptType,309        s.ScriptName310      FROM QBMCustomSQL s311      ORDER BY s.SortOrder312    END313  END TRY314  BEGIN CATCH315    EXEC QBM_PSessionErrorAdd DEFAULT316    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()317    RAISERROR(@Rethrow,318    18,319    1)320      WITH NOWAIT321  END CATCH322END
Open raw exported source
SQL · Raw45 lines
1   create   procedure QBM_PCustomSQLDependencyFill as begin declare @Scriptname nvarchar(255) declare @SQLOrig nvarchar(max) declare @SQLRepl nvarchar2(max) declare @Posi int declare @merken table (UID_QBMCustomSQL varchar(38) collate database_default , scriptname nvarchar(255) collate database_default3, countDeps int, SortOrder int , ScriptCode nvarchar(max)collate database_default, primary key (UID_QBMCustomSQL) ) declare @work table (UID_QBMCustomSQL4 varchar(38) collate database_default , scriptname nvarchar(255) collate database_default, countDeps int, SortOrder int , ScriptCode nvarchar(max)collate5 database_default, primary key (UID_QBMCustomSQL) ) declare @lauf int declare @AnzahlObjekte int declare @CountDeps_neu int declare @Sortorder_neu int 6declare @Geaendert int declare @Schema_Id int declare @AllesOK int  declare @UID_QBMCustomSQL varchar(38) declare @DebugSwitch int = 0 declaRE @CountItems7 INT declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY select @AllesOK = 1 select8 top 1 @Schema_Id = schema_id from sys.schemas where name = N'dbo' if @DebugSwitch > 0 begin print 'Start ' + convert(varchar(64), getutcdate(), 121) end9 insert into @merken(UID_QBMCustomSQL, scriptname, CountDeps, SortOrder) select max(cs.uid_QBMCustomSQL), cs.scriptname, isnull(count(distinct v.object_id10), 0), 1 from QBMCustomSQL cs join sys.objects s on cs.scriptname = s.name collate database_default left outer join sys.sql_dependencies d on s.object_id11 = d.object_id left outer join sys.objects v  on d.referenced_major_id = v.object_id left outer join QBMCustomSQL vs on v.name = vs.Scriptname collate 12database_default and v.schema_id = @Schema_Id where cs.scriptname <> isnull(vs.scriptname, N'') and s.schema_id = @Schema_Id and cs.xtouched <> 'F' and13 cs.scripttype in ('P', 'F', 'V', 'T', 'N') and cs.scriptname not in ('QBM_ZCustomSQLFill', OBJECT_NAME(@@procid)) group by cs.scriptname select @AnzahlObjekte14 = count(*) from @merken if @DebugSwitch > 0 begin print '@merken gefüllt ' + convert(varchar(64), getutcdate(), 121) end update @merken set ScriptCode15 = dbo.QBM_FSQRemoveComment(sc.Scriptcode) from @merken m join qbmCustomsql sc on sc.UID_QBMCustomSQL = m.UID_QBMCustomSQL update @merken set SortOrder16 = patindex(N'%create[ ' + nchar(9) + N']%', Scriptcode) if @DebugSwitch > 0 begin print '@merken dekommentiert ' + convert(varchar(64), getutcdate(), 17121) end delete  @merken where isnull(SortOrder, 0) = 0 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'sätze ohne create gelöscht ' +18 str(@CountItems) end update @merken set ScriptCode = case  when SortOrder = 1 then N'alter' + substring(ScriptCode, 7, 1000000) else  substring(ScriptCode19,1, SortOrder -1)+ N'alter' + substring(ScriptCode, @posi+6, 1000000) end update @merken set SortOrder = 1 if @DebugSwitch > 0 begin print '@merken mit alter '20 + convert(varchar(64), getutcdate(), 121) end select @lauf = 1 while @lauf <= @AnzahlObjekte * 2 and @AllesOK = 1 begin select @geaendert = 0 if @DebugSwitch21 > 0 begin print '####################### neuer Durchlauf '+str(@lauf) + str(@AnzahlObjekte) end  delete  @work  insert @work(ScriptCode, SortOrder, UID_QBMCustomSQL22, countDeps, scriptname) select ScriptCode, SortOrder, UID_QBMCustomSQL, countDeps, scriptname from @merken m  while exists (select top 1 1 from @work 23) and @AllesOK = 1 BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL , @Scriptname = w.scriptname , @SQLOrig = w.ScriptCode from @work w order 24by w.SortOrder, w.scriptname desc if @DebugSwitch > 0 begin print @Scriptname end  BEGIN TRY BEGIN TRY exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement 25= @SQLRepl , @LockTimeout_ms = 50 , @MaxWaitTimeForLock_s = 2 , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 exec QBM_PSessionErrorClean END TRY26 BEGIN CATCH  if @DebugSwitch > 0 begin print @scriptname print isnull(ERROR_MESSAGE() , '<no message>') end update qbmCustomSQL set XTouched = 'F' , XDateUpdated27 = @Xdate , XUserUpdated = @XUser where UID_QBMCustomSQL = @UID_QBMCustomSQL delete  @merken where UID_QBMCustomSQL = @UID_QBMCustomSQL continue  END CATCH28 END TRY BEGIN CATCH  select @AllesOK = 0 END CATCH  if @AllesOK = 0 begin continue end select @CountDeps_neu = null select @CountDeps_neu = isnull(count29(distinct v.object_id), 0) from sys.objects s left outer join sys.sql_dependencies d on s.object_id = d.object_id left outer join sys.objects v  on d.referenced_major_id30 = v.object_id left outer join QBMCustomSQL vs on v.name = vs.Scriptname collate database_default where s.name = @Scriptname and vs.UID_QBMCustomSQL <>31 @UID_QBMCustomSQL and s.schema_id = @Schema_Id and v.schema_id = @Schema_Id group by s.object_id select @CountDeps_neu = isnull(@CountDeps_neu, 0) select32 @Sortorder_neu = null select @Sortorder_neu = isnull(max( vm.SortOrder),0) + 1 from sys.objects s left outer join sys.sql_dependencies d on s.object_id33 = d.object_id left outer join sys.objects v  on d.referenced_major_id = v.object_id left outer join QBMCustomSQL vs on v.name = vs.Scriptname collate 34database_default left outer join @merken vm on vs.UID_QBMCustomSQL = vm.UID_QBMCustomSQL collate database_default where s.name = @Scriptname and vm.scriptname35 <> @Scriptname and s.schema_id = @Schema_Id and v.schema_id = @Schema_Id group by s.name select @Sortorder_neu = isnull(@Sortorder_neu, 1) if @DebugSwitch36 > 0 begin print @scriptname + str(@CountDeps_neu) + str(@Sortorder_neu) end update @merken set countDeps = @CountDeps_neu where UID_QBMCustomSQL = @UID_QBMCustomSQL37 and countDeps <> @CountDeps_neu if @@rowcount > 0 begin if @DebugSwitch > 0 begin print 'count deps geändert' end select @geaendert = 1 end update @merken38 set SortOrder = @Sortorder_neu where UID_QBMCustomSQL = @UID_QBMCustomSQL and SortOrder < @Sortorder_neu if @@rowcount > 0 begin if @DebugSwitch > 0 begin39 print 'SortOrder  geändert' end select @geaendert = 1 end delete  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL END  if @AllesOK = 0 begin continue40 end if @geaendert = 1 begin select @lauf = @lauf +1 end else begin select @lauf = @AnzahlObjekte * 2 +1 end end  update QBMCustomSQl set SortOrder = m.SortOrder41 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMCustomSQl cu join @merken m on cu.UID_QBMCustomSQl = m.UID_QBMCustomSQL where m.SortOrder <> ISNULL42(cu.SortOrder, 0) if @DebugSwitch > 0 begin select s.SortOrder, s.ScriptType, s.ScriptName from QBMCustomSQL s order by s.SortOrder end END TRY BEGIN CATCH43 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 44end 45