Back to OIM Explorer

dbo.QBM_ZCheckStatistics

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.670 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_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL74 lines
1CREATE PROCEDURE QBM_ZCheckStatistics(2  @SlotNumber int,3  @dummy1 varchar(38),4  @dummy2 varchar(38),5  @dummyGenProcID varchar(38)6)7AS8BEGIN9  DECLARE @db nvarchar(128)10  SELECT @db = db_name()11  DECLARE @autocreate int12  SELECT13    @autocreate = convert(int,14    DATABASEPROPERTYEX(@db, N 'IsAutoCreateStatistics'))15  DECLARE @autoupdate int16  SELECT17    @autoupdate = convert(int,18    DATABASEPROPERTYEX(@db, N 'IsAutoupdateStatistics'))19  DECLARE @SQLcmd nvarchar(max)20  DECLARE @muster nvarchar(200)21  DECLARE @ElementBuffer QBM_YCursorBuffer22  DECLARE @ElementCount int23  DECLARE @ElementIndex int24  BEGIN TRY25    SELECT @muster = N 'drop statistics [%schema%].[%table%].[%stat%]'26    IF @autocreate = 0 AND @autoupdate = 027    BEGIN28      INSERT INTO @ElementBuffer(ContentFull)29      SELECT30        replace(replace(replace(@Muster, N '%schema%', sc.name), N '%table%', t.name),31        N '%stat%',32        i.name)33      FROM sys.tables t34      JOIN sys.stats i35        WITH(nolock)36        ON t.object_id = i.Object_id37      JOIN sys.schemas sc38        ON t.schema_id = sc.Schema_id39      WHERE40        i.auto_created = 141      SELECT @ElementCount = @@ROWCOUNT42      SELECT @ElementIndex = 143      WHILE @ElementIndex <= @ElementCount44      BEGIN45        SELECT TOP 1 @SQLcmd = bu.ContentFull46        FROM @ElementBuffer bu47        WHERE48          bu.ElementIndex = @ElementIndex49        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,50          @LockTimeout_ms = DEFAULT,51          @MaxWaitTimeForLock_s = DEFAULT,52          @ProcIDForJournal = @@procid,53          @HandleErrorSilent = 054        SELECT @ElementIndex += 155      END56    END57    IF @autocreate = 158    BEGIN59      EXEC sp_autostats dialogdbqueue,60      'ON'61      EXEC sp_autostats Jobqueue,62      'ON'63      EXEC sp_autostats JobTreeParamColl,64      'ON'65    END66  END TRY67  BEGIN CATCH68    EXEC QBM_PSessionErrorAdd DEFAULT69    RAISERROR('',70    18,71    1)72      WITH NOWAIT73  END CATCH74END
Open raw exported source
SQL ยท Raw12 lines
1   create   procedure QBM_ZCheckStatistics (@SlotNumber int , @dummy1 varchar(38) , @dummy2 varchar(38) , @dummyGenProcID varchar(38) ) as begin2  declare @db nvarchar(128) select @db = db_name() declare @autocreate int select @autocreate = convert(int, DATABASEPROPERTYEX (@db, N'IsAutoCreateStatistics'3)) declare @autoupdate int select @autoupdate = convert(int, DATABASEPROPERTYEX (@db, N'IsAutoupdateStatistics'))  declare @SQLcmd nvarchar(max) declare4 @muster nvarchar(200) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int BEGIN TRY select @muster = N'drop statistics [%schema%].[%table%].[%stat%]'5 if @autocreate = 0 and @autoupdate = 0 begin insert into @ElementBuffer (ContentFull) select replace(replace(replace(@Muster, N'%schema%' , sc.name), 6N'%table%', t.name), N'%stat%', i.name) from sys.tables t join sys.stats i with (nolock) on t.object_id = i.Object_id join sys.schemas sc on t.schema_id7 = sc.Schema_id where i.auto_created = 1  select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select 8top 1 @SQLcmd = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd 9, @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex += 1 end  end10  if @autocreate = 1 begin EXEC sp_autostats dialogdbqueue, 'ON' EXEC sp_autostats Jobqueue, 'ON' EXEC sp_autostats JobTreeParamColl, 'ON' end END TRY 11BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 12