dbo.QBM_PColumnCheckBlobExternal

1      create   procedure QBM_PColumnCheckBlobExternal ( @TableName varchar(38) , @GenProcID varchar(38) ) as begin set nocount on declare @XUser
2 nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @merk table ( columnname varchar(30) collate database_default, datatype
3 varchar(64) collate database_default, mittlereLaenge int, externhalten int default 0, NumDatatype int ) declare  @mittlereLaengeMitBlobs int, @AnzahlBlobs
4 int DECLARE @SQLcmd nvarchar(max);    declare @TableBaseRecordLen int  declare @UID_DialogTable varchar(38) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext
5('') SET XACT_ABORT OFF BEGIN TRY if isnull(@GenProcID, '') = '' begin select @GenProcID = newid() end select top 1 @UID_DialogTable = UID_DialogTable 
6from DialogTable with (readpast) where TableName = @TableName exec QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogcolumn set IsBlobExternal
7 = 0 , XDateUpdated = @Xdate , XUserUpdated = @XUser where IsBlobExternal is null and UID_DialogTable = @UID_DialogTable if exists (select top 1 1 from
8 DialogTable where TableName = @TableName and tabletype in ('M') ) begin  goto ende end if not exists (select top 1 1 from dialogcolumn where UID_DialogTable
9 = @UID_DialogTable and datatype in (10, 7, 6) ) begin  exec QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogcolumn set IsBlobExternal = 0 
10, XDateUpdated = @Xdate , XUserUpdated = @XUser where IsBlobExternal = 1 and UID_DialogTable = @UID_DialogTable goto ende end insert into @merk( columnname
11, mittlereLaenge, datatype, numdatatype) select dc.ColumnName , case when dc.SchemaDataType like '%text%' or dc.SchemaDataType = 'image'  or ( dc.SchemaDataType
12 in ( 'varbinary')  and c.CHARACTER_MAXIMUM_LENGTH = -1 ) then 8000 else dc.AVGDataLenByte end , dc.SchemaDataType, dc.DataType from information_schema.columns
13 c join dialogcolumn dc on c.Table_name = @TableName and dc.UID_DialogTable = @UID_DialogTable and c.column_name = dc.columnname   update @merk set externhalten
14 = 1 where mittlereLaenge > 6000 select top 1 @TableBaseRecordLen = BaseRecordLen from dialogtable where TableName = @TableName if @TableBaseRecordLen 
15= 0  begin update @merk set externhalten = 0 where externhalten > 0 goto publizieren end select @mittlereLaengeMitBlobs = sum(mittlereLaenge) from @merk
16 where externhalten = 0  select @AnzahlBlobs = count(*) from @merk where externhalten = 0  and numdatatype = 10 if @AnzahlBlobs = 0 begin select @AnzahlBlobs
17 = 1 end update @merk set externhalten = 1 where mittlereLaenge > @TableBaseRecordLen and externhalten = 0 and numdatatype = 10 update @merk set externhalten
18 = 2 where numdatatype = 10 and @TableBaseRecordLen > 4000 and @TableBaseRecordLen + mittlereLaenge > 6000 and externhalten = 0 update @merk set externhalten
19 = 3 from @merk m where m.numdatatype in ( 10 ) and @TableBaseRecordLen  + (@mittlereLaengeMitBlobs - @TableBaseRecordLen) / @AnzahlBlobs  + m.mittlereLaenge
20  > 6000 and externhalten = 0  publizieren: update @merk set externhalten = 0 from @merk m where exists (select top 1 1 from  (values ('JobRunparameter'
21, 'Valuetemplate') , ('DialogJournal', 'MessageString') , ('JobParameter' , 'Valuetemplate') , ('JobQueue' , 'ParamIn' ) , ('DialogTaggedItem', 'AlternateKey'
22) , ('JobHistory', 'ParamIn') , ('DialogWatchProperty', 'ContentFull') , ('JobQueue', 'ParamIn') , ('jobtreeparamcoll', 'ParameterValue') , ('DialogNotification'
23, 'WhereClause') ) as v (TableName, ColumnName) where v.TableName = @TableName and v.columnname = m.Columnname ) exec QBM_PSessionContextSet 'GenProcID'
24, @GenProcID update dialogcolumn set IsBlobExternal = sign(m.externhalten), Xdateupdated = GetUTCDate(), XUserUpdated = 'DBScheduler' from DialogColumn
25 c join @merk m on c.UID_DialogTable = @UID_DialogTable and c.ColumnName = m.Columnname where c.isBlobExternal <> sign(m.externhalten) END TRY BEGIN CATCH
26 exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende:  exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end
27 
28