Back to OIM Explorer

dbo.QBM_PTableStatistics

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.912 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_FGISessionContext source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_FGITableSizeMB source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL278 lines
1CREATE PROCEDURE QBM_PTableStatistics(2  @TableName varchar(38),3  @GenProcID varchar(38)4)5AS6BEGIN7  DECLARE @SQLCmd nvarchar(max)8  DECLARE @ColumnName varchar(30)9  DECLARE @SizeMB float10  DECLARE @CountItems int11  DECLARE @SQLDeclare nvarchar(max)12  DECLARE @SQLSelect nvarchar(max)13  DECLARE @SQLUpdate nvarchar(max)14  DECLARE @id int15  DECLARE @DebugSwitch int = 016  DECLARE @UID_DialogTable varchar(38)17  DECLARE @columnNames QBM_YCursorBuffer18  DECLARE @ColumnDatatype varchar(30)19  DECLARE @UID_DialogColumn varchar(38)20  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')21  DECLARE @ElementCount int22  DECLARE @ElementIndex int23  DECLARE @XUser nvarchar(64) = object_name(@@procid)24  DECLARE @Xdate datetime = getutcdate()25  SET XACT_ABORT OFF26  BEGIN TRY27    SELECT @SQLDeclare = N ''28    SELECT @SQLSelect = N ''29    SELECT @SQLUpdate = N ''30    SELECT TOP 1 @UID_DialogTable = UID_DialogTable31    FROM DialogTable32      WITH(readpast)33    WHERE34      TableName = @TableName35    IF 'READ' =(36      SELECT TOP 1 usagetype37      FROM dialogtable38      WITH(readpast)39    WHERE40      TableName = @TableName)41    BEGIN42      IF @DebugSwitch > 043      BEGIN44        Print 'UsageType ''READ'' found'45      END46      EXEC QBM_PSessionContextSet 'GenProcID',47        @GenProcID48      UPDATE dialogtable49      SET SizeMB = 0.0,50      CountRows = 0,51      BaseRecordLen = 0,52      XDateUpdated = @Xdate,53      XUserUpdated = @XUser54      WHERE55        TableName = @TableName AND(isnull(SizeMB, 0.0) <> 0.0 OR isnull(CountRows, 0) = 0 OR isnull(BaseRecordLen,56      0) = 0)57      EXEC QBM_PSessionContextSet 'GenProcID',58        @GenProcID59      UPDATE dialogcolumn60      SET AVGDataLenByte = 0,61      XDateUpdated = @Xdate,62      XUserUpdated = @XUser63      WHERE64        UID_DialogTable = @UID_DialogTable AND isnull(AVGDataLenByte,65      0) <> 066      GOTO ende67    END68    IF NOT EXISTS(69      SELECT TOP 1 170      FROM sys.tables71      WHERE72        name = @TableName)73    BEGIN74      IF @DebugSwitch > 075      BEGIN76        Print 'table not found in sys.tables'77      END78      GOTO ende79    END80    SELECT @CountItems = dbo.QBM_FGITableCountAll(@TableName)81    EXEC QBM_PSessionContextSet 'GenProcID',82      @GenProcID83    UPDATE dialogtable84    SET CountRows = @CountItems85    WHERE86      UID_DialogTable = @UID_DialogTable AND isnull(countRows,87    0) <> @CountItems88    SELECT @id = NULL89    SELECT @id = o.object_id90    FROM sys.tables o91    WHERE92      o.name = @TableName93    IF @id IS NOT NULL94    BEGIN95      IF @DebugSwitch > 096      BEGIN97        Print 'table found in sys.objects'98      END99      SELECT100        @SizeMB = dbo.QBM_FGITableSizeMB(@TableName,101        1)102      EXEC QBM_PSessionContextSet 'GenProcID',103        @GenProcID104      UPDATE dialogtable105      SET SizeMB = @SizeMB106      WHERE107        UID_DialogTable = @UID_DialogTable AND isnull(sizeMB,108      0.0) <> @SizeMB109    END110    IF @DebugSwitch > 0111    BEGIN112      print convert(nvarchar(64),113      GETUTCDATE(),114      121) + '  4'115    END116    SELECT TOP 1 @CountItems = s.avg_record_size_in_bytes117    FROM sys.dm_db_index_physical_stats(db_id(),118    @id,119    NULL,120    NULL,121    'SAMPLED') s122    WHERE123      s.alloc_unit_type_desc = 'IN_ROW_DATA' AND s.index_type_desc = 'CLUSTERED INDEX'124    SELECT125      @CountItems = isnull(@CountItems,126      0)127    EXEC QBM_PSessionContextSet 'GenProcID',128      @GenProcID129    UPDATE dialogTable130    SET BaseRecordLen = @CountItems131    WHERE132      UID_DialogTable = @UID_DialogTable AND isnull(BaseRecordLen,133    0) <> @CountItems134    IF @DebugSwitch > 0135    BEGIN136      print convert(nvarchar(64),137      GETUTCDATE(),138      121) + '  5'139    END140    IF NOT EXISTS(141      SELECT TOP 1 1142      FROM sys.objects o143    LEFT144    OUTER145      JOIN sys.dm_db_index_usage_stats s146        ON s.object_id = o.object_id147      WHERE148        o.name = @TableName AND isnull(s.last_user_update, getutcdate()) > dateadd(hh, -48, GetUTCDate()))149    BEGIN150      GOTO ende151    END152    INSERT INTO @columnNames(UID1,153    UID2,154    UID3)155    SELECT156      c.columnname,157      c.SchemaDataType,158      c.UID_DialogColumn159    FROM dialogColumn c160      WITH(readpast)161    JOIN QBM_VSchemaColumns ic162      ON c.UID_DialogTable = @UID_DialogTable AND ic.table_name = @TableName AND c.columnname = ic.column_name163    WHERE164      c.UID_DialogTable = @UID_DialogTable165    SELECT @ElementCount = @@ROWCOUNT166    SELECT @ElementIndex = 1167    WHILE @ElementIndex <= @ElementCount168    BEGIN169      SELECT170        TOP 1 @columnname = bu.UID1,171        @ColumnDataType = bu.UID2,172        @uid_DialogColumn = bu.UID3173      FROM @columnNames bu174      WHERE175        bu.ElementIndex = @ElementIndex176      IF @DebugSwitch > 0177      BEGIN178        print nchar(9) + @columnname179      END180      SELECT181        @SQLDeclare = @SQLDeclare + N '182		declare @' + @columnname + ' int' + CASE @ColumnDataType183        WHEN 'int' THEN184        ' = 4'185        WHEN 'bigint' THEN186        ' = 8'187        WHEN 'datetime' THEN188        ' = 8'189        WHEN 'float' THEN190        ' = 8'191        WHEN 'bit' THEN192        ' = 1'193      ELSE ''194      END195      IF @ColumnDatatype NOT IN('int',196      'bigint',197      'datetime',198      'float',199      'bit')200      BEGIN201        IF @SQLSelect > N ''202        BEGIN203          SELECT204            @SQLSelect = @SQLSelect + nchar(13) + nchar(10) + N ','205        END206        SELECT207          @SQLSelect = @SQLSelect + N '	@' + @Columnname + ' = convert(int, round(avg(convert(float, isnull(datalength(' + @columnname + N '), 0))) , 0))'208      END209      SELECT210        @SQLupdate = @SQLUpdate + N '211		if exists (select top 1 1212					from dialogcolumn c213					where isnull(AVGDataLenByte, 0) <> @' + @columnname214        + ' 215					and UID_DialogColumn = ''' + @UID_DialogColumn + N '''216					)217		 begin218			update dialogColumn set AVGDataLenByte = @' + @Columnname219        + N '220					where isnull(AVGDataLenByte, 0) <> @' + @columnname + ' 221						and UID_DialogColumn = ''' + @UID_DialogColumn + N '''222		 end223		'224      IF @DebugSwitch > 0225      BEGIN226        print @SQLcmd227      END228      SELECT @ElementIndex += 1229    END230    IF @DebugSwitch > 0231    BEGIN232      print convert(nvarchar(64),233      GETUTCDATE(),234      121) + '  6'235    END236    IF @SQLDeclare > ' '237    BEGIN238      SELECT239        @SQLCmd = CONCAT(@SQLDeclare,240      CASE241        WHEN @SQLSelect > ' ' THEN242      CONCAT(nchar(13), nchar(10), ' select ', @SQLSelect, ' from ', @TableName, nchar(13), nchar(10))243      ELSE ' '244      END,245      @SQLUpdate)246      IF @DebugSwitch > 0247      BEGIN248        SELECT249          @SQLCmd = @SQLCmd + '250		 print convert(nvarchar(64), GETUTCDATE(), 121) + ''  select fertig'''251      END252    END253    EXEC QBM_PSessionContextSet 'GenProcID',254      @GenProcID255    IF @DebugSwitch > 0256    BEGIN257      print @SQLcmd258    END259    EXEC sp_executeSQL @SQLCmd260    IF @DebugSwitch > 0261    BEGIN262      print convert(nvarchar(64),263      GETUTCDATE(),264      121) + '  7'265    END266  END TRY267  BEGIN CATCH268    EXEC QBM_PSessionErrorAdd DEFAULT269    RAISERROR('',270    18,271    1)272      WITH NOWAIT273  END CATCH274  ende:275  EXEC QBM_PSessionContextSet 'GenProcID',276    @GenProcID_R277  RETURN278END
Open raw exported source
SQL ยท Raw51 lines
1       create   procedure QBM_PTableStatistics (@TableName varchar(38) , @GenProcID varchar(38) ) as begin declare @SQLCmd nvarchar(max) declare2 @ColumnName varchar(30) declare @SizeMB float declare @CountItems int declare @SQLDeclare nvarchar(max) declare @SQLSelect nvarchar(max) declare @SQLUpdate3 nvarchar(max) declare @id int declare @DebugSwitch int = 0 declare @UID_DialogTable varchar(38)   declare @columnNames QBM_YCursorBuffer declare @ColumnDatatype4 varchar(30) declare @UID_DialogColumn varchar(38) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementCount int declare @ElementIndex5 int declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY select @SQLDeclare = N'' select6 @SQLSelect = N'' select @SQLUpdate = N'' select top 1 @UID_DialogTable = UID_DialogTable from DialogTable with (readpast) where TableName = @TableName7  if 'READ' = ( select top 1 usagetype from dialogtable with (readpast) where TableName = @TableName ) begin if @DebugSwitch > 0 begin Print 'UsageType ''READ'' found'8 end exec QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogtable set SizeMB = 0.0, CountRows = 0, BaseRecordLen = 0   , XDateUpdated = @Xdate9 , XUserUpdated = @XUser where TableName = @TableName and ( isnull(SizeMB, 0.0) <> 0.0 OR isnull(CountRows, 0) = 0 OR isnull(BaseRecordLen, 0) = 0 ) exec10 QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogcolumn set AVGDataLenByte = 0  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_DialogTable11 = @UID_DialogTable and isnull(AVGDataLenByte,0) <> 0 goto ende end if not exists (select top 1 1 from sys.tables where name = @TableName ) begin if @DebugSwitch12 > 0 begin Print 'table not found in sys.tables' end goto ende end select @CountItems = dbo.QBM_FGITableCountAll(@TableName ) exec QBM_PSessionContextSet13 'GenProcID', @GenProcID update dialogtable set CountRows = @CountItems where UID_DialogTable = @UID_DialogTable and isnull(countRows,0) <> @CountItems14  select @id = null select @id = o.object_id from sys.tables o where o.name = @TableName if @id is not null  begin if @DebugSwitch > 0 begin Print 'table found in sys.objects'15 end select @SizeMB = dbo.QBM_FGITableSizeMB(@TableName, 1) exec QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogtable set SizeMB = @SizeMB16 where UID_DialogTable = @UID_DialogTable and isnull(sizeMB, 0.0) <> @SizeMB end if @DebugSwitch > 0 begin print convert(nvarchar(64), GETUTCDATE(), 12117) + '  4' end  select top 1 @CountItems = s.avg_record_size_in_bytes from sys.dm_db_index_physical_stats(db_id(), @id, null, null, 'SAMPLED') s where s.alloc_unit_type_desc18 = 'IN_ROW_DATA' and s.index_type_desc = 'CLUSTERED INDEX'  select @CountItems = isnull(@CountItems, 0) exec QBM_PSessionContextSet 'GenProcID', @GenProcID19 update dialogTable set BaseRecordLen = @CountItems where UID_DialogTable = @UID_DialogTable and isnull(BaseRecordLen, 0) <> @CountItems if @DebugSwitch20 > 0 begin print convert(nvarchar(64), GETUTCDATE(), 121) + '  5' end  if not exists ( select top 1 1 from sys.objects o left outer join sys.dm_db_index_usage_stats21 s on s.object_id = o.object_id where o.name = @TableName and isnull(s.last_user_update, getutcdate()) > dateadd(hh, -48, GetUTCDate()) ) begin goto ende22 end insert into @columnNames(UID1  , UID2  , UID3 ) select c.columnname , c.SchemaDataType, c.UID_DialogColumn from dialogColumn c with (readpast) join23 QBM_VSchemaColumns ic on c.UID_DialogTable = @UID_DialogTable and ic.table_name = @TableName and c.columnname = ic.column_name where c.UID_DialogTable24 = @UID_DialogTable select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @columnname = bu.UID125 , @ColumnDataType = bu.UID2 , @uid_DialogColumn = bu.UID3 from @columnNames bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print nchar26(9) + @columnname end select @SQLDeclare = @SQLDeclare + N'27		declare @' + @columnname + ' int' + case @ColumnDataType when 'int' then ' = 4' when 'bigint'28 then ' = 8' when 'datetime' then ' = 8' when 'float' then ' = 8' when 'bit' then ' = 1' else '' end if @ColumnDatatype not in ('int', 'bigint', 'datetime'29, 'float', 'bit') begin if @SQLSelect > N'' begin select @SQLSelect = @SQLSelect + nchar(13) + nchar(10) + N',' end select @SQLSelect = @SQLSelect + N'	@'30 + @Columnname + ' = convert(int, round(avg(convert(float, isnull(datalength(' + @columnname + N'), 0))) , 0))' end select @SQLupdate = @SQLUpdate + N'31		if exists (select top 1 132					from dialogcolumn c33					where isnull(AVGDataLenByte, 0) <> @'34 + @columnname + ' 35					and UID_DialogColumn = ''' + @UID_DialogColumn + N'''36					)37		 begin38			update dialogColumn set AVGDataLenByte = @' + @Columnname39 + N'40					where isnull(AVGDataLenByte, 0) <> @' + @columnname + ' 41						and UID_DialogColumn = ''' + @UID_DialogColumn + N'''42		 end43		' if @DebugSwitch44 > 0 begin print @SQLcmd end  select @ElementIndex += 1 end  if @DebugSwitch > 0 begin print convert(nvarchar(64), GETUTCDATE(), 121) + '  6' end if @SQLDeclare45 > ' ' begin select @SQLCmd = concat( @SQLDeclare , case when @SQLSelect > ' ' then concat( nchar(13), nchar(10), ' select ', @SQLSelect , ' from ' , @TableName46 , nchar(13), nchar(10)) else ' ' end , @SQLUpdate ) if @DebugSwitch > 0 begin select @SQLCmd = @SQLCmd + '47		 print convert(nvarchar(64), GETUTCDATE(), 121) + ''  select fertig'''48 end end  exec QBM_PSessionContextSet 'GenProcID', @GenProcID if @DebugSwitch > 0 begin print @SQLcmd end exec sp_executeSQL @SQLCmd if @DebugSwitch > 490 begin print convert(nvarchar(64), GETUTCDATE(), 121) + '  7' end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT50 END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 51