dbo.QBM_PTableStatistics

1       create   procedure QBM_PTableStatistics (@TableName varchar(38) , @GenProcID varchar(38) ) as begin declare @SQLCmd nvarchar(max) declare
2 @ColumnName varchar(30) declare @SizeMB float declare @CountItems int declare @SQLDeclare nvarchar(max) declare @SQLSelect nvarchar(max) declare @SQLUpdate
3 nvarchar(max) declare @id int declare @DebugSwitch int = 0 declare @UID_DialogTable varchar(38)   declare @columnNames QBM_YCursorBuffer declare @ColumnDatatype
4 varchar(30) declare @UID_DialogColumn varchar(38) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementCount int declare @ElementIndex
5 int declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY select @SQLDeclare = N'' select
6 @SQLSelect = N'' select @SQLUpdate = N'' select top 1 @UID_DialogTable = UID_DialogTable from DialogTable with (readpast) where TableName = @TableName
7  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 = @Xdate
9 , XUserUpdated = @XUser where TableName = @TableName and ( isnull(SizeMB, 0.0) <> 0.0 OR isnull(CountRows, 0) = 0 OR isnull(BaseRecordLen, 0) = 0 ) exec
10 QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogcolumn set AVGDataLenByte = 0  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_DialogTable
11 = @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 @DebugSwitch
12 > 0 begin Print 'table not found in sys.tables' end goto ende end select @CountItems = dbo.QBM_FGITableCountAll(@TableName ) exec QBM_PSessionContextSet
13 'GenProcID', @GenProcID update dialogtable set CountRows = @CountItems where UID_DialogTable = @UID_DialogTable and isnull(countRows,0) <> @CountItems
14  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 = @SizeMB
16 where UID_DialogTable = @UID_DialogTable and isnull(sizeMB, 0.0) <> @SizeMB end if @DebugSwitch > 0 begin print convert(nvarchar(64), GETUTCDATE(), 121
17) + '  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_desc
18 = 'IN_ROW_DATA' and s.index_type_desc = 'CLUSTERED INDEX'  select @CountItems = isnull(@CountItems, 0) exec QBM_PSessionContextSet 'GenProcID', @GenProcID
19 update dialogTable set BaseRecordLen = @CountItems where UID_DialogTable = @UID_DialogTable and isnull(BaseRecordLen, 0) <> @CountItems if @DebugSwitch
20 > 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_stats
21 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 ende
22 end insert into @columnNames(UID1  , UID2  , UID3 ) select c.columnname , c.SchemaDataType, c.UID_DialogColumn from dialogColumn c with (readpast) join
23 QBM_VSchemaColumns ic on c.UID_DialogTable = @UID_DialogTable and ic.table_name = @TableName and c.columnname = ic.column_name where c.UID_DialogTable
24 = @UID_DialogTable select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @columnname = bu.UID1
25 , @ColumnDataType = bu.UID2 , @uid_DialogColumn = bu.UID3 from @columnNames bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print nchar
26(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 1
32					from dialogcolumn c
33					where isnull(AVGDataLenByte, 0) <> @'
34 + @columnname + ' 
35					and UID_DialogColumn = ''' + @UID_DialogColumn + N'''
36					)
37		 begin
38			update dialogColumn set AVGDataLenByte = @' + @Columnname
39 + N'
40					where isnull(AVGDataLenByte, 0) <> @' + @columnname + ' 
41						and UID_DialogColumn = ''' + @UID_DialogColumn + N'''
42		 end
43		' if @DebugSwitch
44 > 0 begin print @SQLcmd end  select @ElementIndex += 1 end  if @DebugSwitch > 0 begin print convert(nvarchar(64), GETUTCDATE(), 121) + '  6' end if @SQLDeclare
45 > ' ' begin select @SQLCmd = concat( @SQLDeclare , case when @SQLSelect > ' ' then concat( nchar(13), nchar(10), ' select ', @SQLSelect , ' from ' , @TableName
46 , 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 NOWAIT
50 END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 
51