Back to OIM Explorer

dbo.QBM_FTColumnsToWatch

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 4.403 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGIConfigparmValue source text reference

References

dbo.QBM_FGIConfigparmValue

Referenced By

Complete Source

1CREATE FUNCTION dbo.QBM_FTColumnsToWatch(2  @TableName varchar(32),3  @Operation varchar(16)4) RETURNS @erg TABLE(TableName nvarchar(32) collate database_default,5ColumnName nvarchar(32) collate database_default,6TableNameView nvarchar(32) collate database_default,7ColumnNameView nvarchar(32) collate database_default8)9AS10BEGIN11  DECLARE @UeberhauptWas int = 012  IF '' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog')13  BEGIN14    GOTO ende15  END16  IF NOT EXISTS(17    SELECT TOP 1 118    FROM DialogColumn c19    WITH(readpast)20  JOIN DialogTable t21    WITH(readpast)22    ON c.UID_DialogTable = t.UID_DialogTable23  WHERE24    t.TableName = @TableName AND c.ColumnName = 'XObjectKey')25  BEGIN26    GOTO ende27  END28  INSERT INTO @erg(TableName,29  ColumnName,30  TableNameView,31  ColumnNameView)32  SELECT33    bt.TableName,34    bc.ColumnName,35    t.TableName,36    c.ColumnName37  FROM DialogColumn bc38    WITH(readpast)39  JOIN DialogTable bt40    WITH(readpast)41    ON bc.UID_DialogTable = bt.UID_DialogTable AND bt.TableName = @TableName AND bc.IsDeactivatedByPreProcessor = 0 AND bt.IsDeactivatedByPreProcessor42  = 043  JOIN DialogTable t44    WITH(readpast)45    ON (t.TableType = 'T' AND t.UID_DialogTable = bt.UID_DialogTable OR t.TableType = 'V' AND t.UID_DialogTableBase = bt.UID_DialogTable) AND t.IsDeactivatedByPreProcessor46  = 047  JOIN DialogColumn c48    WITH(readpast)49    ON c.UID_DialogTable = t.UID_DialogTable AND(t.TableType = 'T' AND c.ColumnName = bc.ColumnName OR t.TableType = 'V' AND c.UID_BaseColumn = bc.UID_DialogColumn50  ) AND c.IsDeactivatedByPreProcessor = 051  WHERE52    ((c.IsToWatch = 1 AND @Operation IN('INSERT', 'UPDATE') AND t.UsageType IN('CONFIGURATION', 'USERDATA',53  'MATERIAL')) OR(c.IsToWatchDelete = 1 AND @Operation IN('DELETE') AND(t.UsageType IN('CONFIGURATION',54  'USERDATA', 'MATERIAL') OR(t.UsageType = 'HISTORY' AND55  LEFT(t.UID_DialogTable, 3) <> 'QBM'))))56  SELECT @UeberhauptWas = @@ROWCOUNT57  IF @UeberhauptWas > 058  BEGIN59    INSERT INTO @erg(TableName,60    ColumnName,61    TableNameView,62    ColumnNameView)63    SELECT64      bt.TableName,65      bc.ColumnName,66      t.TableName,67      c.ColumnName68    FROM DialogColumn bc69      WITH(readpast)70    JOIN DialogTable bt71      WITH(readpast)72      ON bc.UID_DialogTable = bt.UID_DialogTable AND bt.TableName = @TableName73    JOIN DialogTable t74      WITH(readpast)75      ON (t.TableType = 'T' AND t.UID_DialogTable = bt.UID_DialogTable OR t.TableType = 'V' AND t.UID_DialogTableBase = bt.UID_DialogTable)76    JOIN DialogColumn c77      WITH(readpast)78      ON c.UID_DialogTable = t.UID_DialogTable AND(t.TableType = 'T' AND c.ColumnName = bc.ColumnName OR t.TableType = 'V' AND c.UID_BaseColumn = bc.UID_DialogColumn79    )80    WHERE81      c.IsAlternatePKMember = 1 AND t.IsTransportDisabled = 0 AND(82    LEFT(t.UID_DialogTable, 3) = 'QBM' AND '1' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog\AutoTrackAlternatePK') OR83    LEFT(t.UID_DialogTable, 3) <> 'QBM' AND '1' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog\AutoTrackAlternatePK\PayLoad')) AND84    NOT EXISTS(85    SELECT TOP 1 186    FROM @erg e87    WHERE88      e.ColumnName = c.ColumnName AND e.TableName = @TableName)89  END90  IF @UeberhauptWas > 0 AND @Operation = 'Delete'91  BEGIN92    INSERT INTO @erg(TableName,93    ColumnName,94    TableNameView,95    ColumnNameView)96    SELECT97      bt.TableName,98      bc.ColumnName,99      t.TableName,100      c.ColumnName101    FROM DialogColumn bc102      WITH(readpast)103    JOIN DialogTable bt104      WITH(readpast)105      ON bc.UID_DialogTable = bt.UID_DialogTable AND bt.TableName = @TableName AND bc.IsDeactivatedByPreProcessor = 0 AND bt.IsDeactivatedByPreProcessor106    = 0107    JOIN DialogTable t108      WITH(readpast)109      ON (t.TableType = 'T' AND t.UID_DialogTable = bt.UID_DialogTable OR t.TableType = 'V' AND t.UID_DialogTableBase = bt.UID_DialogTable) AND t.IsDeactivatedByPreProcessor110    = 0111    JOIN DialogColumn c112      WITH(readpast)113      ON c.UID_DialogTable = t.UID_DialogTable AND(t.TableType = 'T' AND c.ColumnName = bc.ColumnName OR t.TableType = 'V' AND c.UID_BaseColumn = bc.UID_DialogColumn114    ) AND c.IsDeactivatedByPreProcessor = 0115    WHERE116      c.IsPKMember = 1 AND NOT EXISTS(117    SELECT TOP 1 1118    FROM @erg e119    WHERE120      e.TableName = bt.TableName AND e.ColumnName = bc.ColumnName AND e.TableNameView = t.TableName AND e.ColumnNameView = c.ColumnName)121  END122  DELETE @erg123  FROM @erg e124  WHERE125    EXISTS(126  SELECT TOP 1 1127  FROM DialogColumn cc128    WITH(readpast)129  JOIN DialogTable t130    WITH(readpast)131    ON cc.UID_DialogTable = t.UID_DialogTable132  WHERE133    e.TableName = t.TableName AND e.ColumnName= cc.COLUMNNAME AND cc.SchemaDataType IN('text', 'ntext',134  'image')) ende:135  RETURN136END

Open raw exported source

1create function dbo.QBM_FTColumnsToWatch(@TableName varchar(32) , @Operation varchar(16) ) returns @erg table ( TableName nvarchar(32) collate database_default2 , ColumnName nvarchar(32) collate database_default , TableNameView nvarchar(32) collate database_default , ColumnNameView nvarchar(32) collate database_default3 ) as begin declare @UeberhauptWas int = 0 if '' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog') begin goto ende end if not exists (select4 top 1 1 from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @TableName5 and c.ColumnName = 'XObjectKey' ) begin goto ende end   insert into @erg (TableName, ColumnName, TableNameView, ColumnNameView) select bt.TableName, bc.ColumnName6, t.TableName, c.ColumnName from DialogColumn bc with (readpast) join DialogTable bt with (readpast) on bc.UID_DialogTable = bt.UID_DialogTable and bt.TableName7 = @TableName and bc.IsDeactivatedByPreProcessor = 0 and bt.IsDeactivatedByPreProcessor = 0 join DialogTable t with (readpast) on ( t.TableType = 'T' and8 t.UID_DialogTable = bt.UID_DialogTable or t.TableType = 'V' and t.UID_DialogTableBase = bt.UID_DialogTable ) and t.IsDeactivatedByPreProcessor = 0 join9 DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and ( t.TableType = 'T' and c.ColumnName = bc.ColumnName or t.TableType = 'V' 10and c.UID_BaseColumn = bc.UID_DialogColumn ) and c.IsDeactivatedByPreProcessor = 0 where ( (c.IsToWatch = 1 and @Operation in ('INSERT', 'UPDATE') and 11t.UsageType in ('CONFIGURATION', 'USERDATA', 'MATERIAL') ) or (c.IsToWatchDelete = 1 and @Operation in ('DELETE') and ( t.UsageType in ('CONFIGURATION'12, 'USERDATA', 'MATERIAL') or ( t.UsageType = 'HISTORY'  and left(t.UID_DialogTable, 3) <> 'QBM' ) ) ) ) select @UeberhauptWas = @@ROWCOUNT if @UeberhauptWas13 > 0 begin insert into @erg (TableName, ColumnName, TableNameView, ColumnNameView) select bt.TableName, bc.ColumnName, t.TableName, c.ColumnName from DialogColumn14 bc with (readpast) join DialogTable bt with (readpast) on bc.UID_DialogTable = bt.UID_DialogTable and bt.TableName = @TableName join DialogTable t with15 (readpast) on ( t.TableType = 'T' and t.UID_DialogTable = bt.UID_DialogTable or t.TableType = 'V' and t.UID_DialogTableBase = bt.UID_DialogTable ) join16 DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and ( t.TableType = 'T' and c.ColumnName = bc.ColumnName or t.TableType = 'V' 17and c.UID_BaseColumn = bc.UID_DialogColumn ) where c.IsAlternatePKMember = 1 and t.IsTransportDisabled = 0 and ( left(t.UID_DialogTable, 3) = 'QBM' and18 '1' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog\AutoTrackAlternatePK') or left(t.UID_DialogTable, 3) <> 'QBM' and '1' = dbo.QBM_FGIConfigparmValue19('Common\ProcessState\PropertyLog\AutoTrackAlternatePK\PayLoad') ) and Not exists (select top 1 1 from @erg e where e.ColumnName = c.ColumnName and e.TableName20 = @TableName ) end  if @UeberhauptWas > 0 and @Operation = 'Delete' begin insert into @erg (TableName, ColumnName, TableNameView, ColumnNameView) select21 bt.TableName, bc.ColumnName, t.TableName, c.ColumnName from DialogColumn bc with (readpast) join DialogTable bt with (readpast) on bc.UID_DialogTable 22= bt.UID_DialogTable and bt.TableName = @TableName and bc.IsDeactivatedByPreProcessor = 0 and bt.IsDeactivatedByPreProcessor = 0 join DialogTable t with23 (readpast) on ( t.TableType = 'T' and t.UID_DialogTable = bt.UID_DialogTable or t.TableType = 'V' and t.UID_DialogTableBase = bt.UID_DialogTable ) and24 t.IsDeactivatedByPreProcessor = 0 join DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and ( t.TableType = 'T' and c.ColumnName25 = bc.ColumnName or t.TableType = 'V' and c.UID_BaseColumn = bc.UID_DialogColumn ) and c.IsDeactivatedByPreProcessor = 0 where c.IsPKMember = 1 and not26 exists (Select top 1 1 from @erg e where e.TableName = bt.TableName and e.ColumnName = bc.ColumnName and e.TableNameView = t.TableName and e.ColumnNameView27 = c.ColumnName ) end delete  @erg from @erg e where exists (select top 1 1 from DialogColumn cc with (readpast) join DialogTable t with (readpast) on 28cc.UID_DialogTable = t.UID_DialogTable where e.TableName = t.TableName and e.ColumnName= cc.COLUMNNAME and cc.SchemaDataType in ('text', 'ntext', 'image'29)  ) ende: return end 30