Back to OIM Explorer

dbo.QBM_PForeignKeyWrongReport

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 18.822 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_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FCVObjectKeyToModuleOwner source text reference
  • references source dbo.QBM_FCVStringToGUID source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PForeignKeyEmptySetNull source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL746 lines
1CREATE PROCEDURE QBM_PForeignKeyWrongReport(2  @ChildTablePattern varchar(32) = '%',3  @ChildColumnPattern varchar(32) = '%',4  @ParentTablePattern varchar(32) = '%',5  @ParentColumnPattern varchar(32) = '%',6  @LogType varchar(16) = 'FULL',7  @OwnerOfParent varchar(3) = '%',8  @FilterByModuleGuidAllowed BIT = 0,9  @CheckOutsideValues BIT = 010)11AS12BEGIN13  DECLARE @ParentTable nvarchar(64),14  @ChildTable nvarchar(64),15  @ParentColumn nvarchar(64),16  @ChildColumn nvarchar(64),17  @RepairMethod nvarchar(16),18  @RelationID nvarchar(64),19  @IsDynamicFK int,20  @ParentTableBase varchar(30)21  DECLARE @ParentRestriction nvarchar(16)22  DECLARE @isNullableColumn nvarchar(10)23  DECLARE @isPKMember BIT24  DECLARE @CmdCheck nvarchar(max)25  DECLARE @CmdWork nvarchar(max)26  DECLARE @CmdLog nvarchar(max)27  DECLARE @ElementCount int28  DECLARE @ElementIndex int29  DECLARE @ElementBufferMulti QBM_YCursorBuffer30  DECLARE @erg TABLE(ChildTable nvarchar(32) collate database_default,31  ChildColumn nvarchar(32) collate database_default,32  ParentTable nvarchar(32) collate database_default,33  ParentColumn nvarchar(32) collate database_default,34  IsChildPKMember BIT,35  ParentRestriction nchar(2) collate database_default,36  IsChildNullable varchar(16) collate database_default,37  RepairMethod nvarchar(7) collate database_default,38  RelationID nvarchar(64) collate database_default,39  OwnerOfReference varchar(3) collate database_default,40  InvalidValue varchar(140) collate database_default)41  DECLARE @RelationswithWrong QBM_YParameterList42  DECLARE @DebugSwitch int = 043  DECLARE @DebugTime datetime = getutcdate()44  DECLARE @CountRows int = 045  DECLARE @SQLOutsideValues nvarchar(max)46  DECLARE @WerOutsideValues QBM_YParameterList47  DECLARE @ElementCountOutsideValues int48  DECLARE @ElementIndexOutsideValues int49  DECLARE @ElementBufferOutsideValues QBM_YCursorBuffer50  SET XACT_ABORT OFF51  BEGIN TRY52    SET nocount53      ON drop TABLE54    IF EXISTS #TablesForOutside55    CREATE TABLE #TablesForOutside(56      KeyPattern varchar(138) collate database_default NOT NULL primary key57    )58    INSERT INTO #TablesForOutside(KeyPattern59  )60  SELECT '<Key><T>' + t.TableName + '</T>'61  FROM DialogTable t62  EXEC QBM_PForeignKeyEmptySetNull @ChildTablePattern63  SELECT64    @cmdCheck = CONCAT('65if exists (select top 1 166			from @ChildTable with (readpast)67			where not exists (select top 1 168								from @ParentTable p with (readpast)69								where p.@ParentColumn = @ChildTable.@ChildColumn70							) 71			 and @ChildTable.@ChildColumn > '' ''72%dyn%		 and @ChildTable.@ChildColumn like ''<Key><T>@ParentTable</T>%'' 73'74    ,75  CASE76    WHEN @OwnerOfParent = '%' THEN77  ''78  ELSE '79%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''80%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''81'82  END,83  '84			) 85 begin86	select ''@RelationID''87 end88else89 begin90	select '''' where 1 = 0 -- ist nur wegen indirekt91 end92'93)94DECLARE @CmdCheckDynamicOutOfValid nvarchar(max95) = CONCAT('96if exists (select top 1 197			from @ChildTable x with (readpast) left outer join (98												select tp.TableName99												from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn100																			join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable101																			join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable102												where t.TableName = ''@ChildTable''103												 and c.ColumnName = ''@ChildColumn''104												) as y on x.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 105			where y.TableName is null106			 and x.@ChildColumn > '' ''107			 '108,109CASE110  WHEN @OwnerOfParent = '%' THEN111''112ELSE '113			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''114'115END,116'117			) 118 begin119	select ''DynamicFK out of''120 end121else122 begin123	select '''' where 1 = 0 -- ist nur wegen indirekt124 end125126'127)128DECLARE @CmdCheckDynamicOutOfTables nvarchar(max129) = CONCAT('130131if exists (select top 1 1132			from @ChildTable x with (readpast)133			where x.@ChildColumn > '' ''134			 and not exists ( select top 1 1135								from #TablesForOutside a with (forceseek) 136								where left(x.@ChildColumn,  patindex(''%</T>%'', x.@ChildColumn ) + 3 ) =  a.KeyPattern 137							)138			 '139,140CASE141  WHEN @OwnerOfParent = '%' THEN142''143ELSE '144			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''145'146END,147'148			) 149 begin150	select ''DynamicFK all''151 end152else153 begin154	select '''' where 1 = 0 -- ist nur wegen indirekt155 end156157'158)159SELECT160  @CmdLog = CONCAT('	select 161		 ''@ChildTable''162		, ''@ChildColumn''163		, ''@ParentTable''164		, ''@ParentColumn''165		, @IsChildPKMember166		, ''@ParentRestriction''167		, ''@IsChildNullable''168		, ''@RepairMethod''169		, ''@RelationID''170		, case dbo.QBM_FCVGUIDToModuleOwner(@ChildColumn)171				when ''ZZZ'' then left(@ChildColumn, 3)172				else dbo.QBM_FCVGUIDToModuleOwner(@ChildColumn)173		  end174		, @ChildColumn175					from @ChildTable with (readpast)176		where not exists (select top 1 1177							from @ParentTable p with (readpast)178							where p.@ParentColumn = @ChildTable.@ChildColumn) 179%base%			and not exists (select top 1 1 from @ParentBaseTable b with (readpast) where b.@ParentColumn = @ChildTable.@ChildColumn) 180	-- und der Childschlssel ist nicht leer181				 and @ChildTable.@ChildColumn > '' ''182%dyn%		 and @ChildTable.@ChildColumn like ''<Key><T>@ParentTable</T>%'' 183'184  ,185CASE186  WHEN @OwnerOfParent = '%' THEN187''188ELSE '189%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''190%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''191'192END,193'194'195)196DECLARE @CmdLogDynamicOutOfValid nvarchar(max197) = CONCAT('	select 198		 ''@ChildTable''199		, ''@ChildColumn''200		, dbo.QBM_FCVObjectKeyToElement(''TableName'', x.@ChildColumn)201		, ''XObjectKey''202		, @IsChildPKMember203		, ''@ParentRestriction''204		, ''@IsChildNullable''205		, ''@RepairMethod''206		, ''@RelationID''207		, case dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)208				when ''ZZZ'' then ''CCC''209				else dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)210		  end211		, @ChildColumn212					from @ChildTable x with (readpast) left outer join (213												select tp.TableName214												from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn215																			join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable216																			join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable217												where t.TableName = ''@ChildTable''218												 and c.ColumnName = ''@ChildColumn''219												) as y on x.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 220			where y.TableName is null221			and x.@ChildColumn > '' ''222			'223,224CASE225  WHEN @OwnerOfParent = '%' THEN226''227ELSE '228			and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''229'230END,231'232'233)234DECLARE @CmdLogDynamicOutOfTables nvarchar(max235) = CONCAT('	236		select 237		 ''@ChildTable''238		, ''@ChildColumn''239		, dbo.QBM_FCVObjectKeyToElement(''TableName'', x.@ChildColumn)240		, ''XObjectKey''241		, @IsChildPKMember242		, ''@ParentRestriction''243		, ''@IsChildNullable''244		, ''@RepairMethod''245		, ''@RelationID''246		, case dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)247				when ''ZZZ'' then ''CCC''248				else dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)249		  end250		, @ChildColumn251					from @ChildTable x with (readpast)252			where x.@ChildColumn > '' ''253			 and not exists ( select top 1 1254								from #TablesForOutside a with (forceseek) 255								where left(x.@ChildColumn,  patindex(''%</T>%'', x.@ChildColumn ) + 3 ) =  a.KeyPattern 256							)257			 '258,259CASE260  WHEN @OwnerOfParent = '%' THEN261''262ELSE '263			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''264'265END,266'267'268)269DECLARE @CmdCheckDynamicOutSide nvarchar(max270) = CONCAT('271if exists (select top 1 1272			from @ChildTable x with (readpast) left outer join @ParentBaseTable p with (readpast) on x.@ChildColumn = p.XObjectKey273				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''274				and p.XObjectKey is null					275				and x.@ChildColumn > '' ''276				'277,278CASE279  WHEN @OwnerOfParent = '%' THEN280''281ELSE '282				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''283'284END,285'286			) 287 begin288	select ''DynamicFK outside''289 end290else291 begin292	select '''' where 1 = 0 -- ist nur wegen indirekt293 end294295'296)297DECLARE @CmdLogDynamicOutSide nvarchar(max298) = CONCAT('	select 299		 ''@ChildTable''300		, ''@ChildColumn''301		, dbo.QBM_FCVObjectKeyToElement(''TableName'', x.@ChildColumn)302		, ''XObjectKey''303		, 0304		, ''@ParentRestriction''305		, ''@IsChildNullable''306		, ''@RepairMethod''307		, ''@RelationID''308		, case dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)309				when ''ZZZ'' then ''CCC''310				else dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)311		  end312		, @ChildColumn313				from @ChildTable x with (readpast) left outer join @ParentBaseTable p with (readpast) on x.@ChildColumn = p.XObjectKey314				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''315				and p.XObjectKey is null					316				and x.@ChildColumn > '' ''317				'318,319CASE320  WHEN @OwnerOfParent = '%' THEN321''322ELSE '323				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''324'325END,326'327'328)329DELETE @ElementBufferMulti330INSERT INTO @ElementBufferMulti(Ident1,331Ident2,332Ident3,333LongIdent1,334Bit1,335UID1,336UID3,337UID2,338LongIdent2,339Int2,340UID4341)342SELECT343  ParentTable,344  ChildTable,345  ParentColumn,346  ChildColumn,347  IsChildPKMember,348  ParentRestriction,349  IsChildNullable,350  RepairMethod,351  RelationID,352  IsDynamicFK,353  fr.ParenttableBase354FROM QBM_VForeignKeyRepairable fr355WHERE356  fr.ChildTable LIKE @ChildTablePattern AND fr.ChildColumn LIKE @ChildColumnPattern AND(fr.ParentTable LIKE @ParentTablePattern OR fr.Parenttable357= '%'358) AND fr.ParentColumn LIKE @ParentColumnPattern AND(@FilterByModuleGuidAllowed = 0 OR @FilterByModuleGuidAllowed = 1 AND fr.IsParentTabModuleGUIDAllowed359= 1360)361ORDER BY fr.ChildTable,362fr.ChildColumn,363fr.ParentTable,364fr.ParentColumn365SELECT @ElementCount = @@ROWCOUNT366IF @CheckOutsideValues = 1367BEGIN368  INSERT INTO @ElementBufferOutsideValues(ContentFull369)370SELECT371  '372		select distinct t.tablename as tabletarget, ''' + yt.TableName + ''', ''' + yc.ColumnName + '''373			from ' + yt.TableName + ' x with (readpast) join DialogTable t with (readpast) on x.'374  + yc.ColumnName + ' like ''<Key><T>'' + t.TableName + ''</T><P>%''375			'376FROM DialogColumn yc377  WITH(readpast378)379JOIN DialogTable yt380  WITH(readpast381)382  ON yc.UID_DialogTable = yt.UID_DialogTable AND yt.TableType IN('V',383'T'384) AND yt.UsageType NOT IN('HISTORY'385)386LEFT387OUTER388JOIN DialogValidDynamicRef r389  WITH(readpast390)391  ON yc.UID_DialogColumn = r.UID_DialogColumn392WHERE393  yc.IsDynamicFK = 1 AND r.UID_DialogColumn IS NULL AND yc.SchemaDataLen = 138 AND yc.SchemaDataType = 'varchar' AND yc.ColumnName <> 'XObjectKey' AND394NOT EXISTS(395SELECT TOP 1 1396FROM(397VALUES('QER-4E75B64F5997406CAC342BAB6A46F8BE'),398('QER-27E0BCAF583B480499300CFF790725E7'),399('QER-9C38234735B84413BE4C612A1A7F7324'),400('QER-36A10DEFD23E469C8132EFB58032DD85'),401('HDB-B6167ABE67A74F8DB42E2CBD2D50CE56'),402('HDB-3935E716EDB64CB587D2ECA6F93ABEA9'),403('QBM-9802EB4D704745458CD18D6850B4909A'))404AS405ex(UID_DialogColumn)406WHERE407  ex.UID_DialogColumn = yc.UID_DialogColumn) AND NOT(yc.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7' AND dbo.QBM_FGIDBOwner() =408'CCC')409SELECT @ElementCountOutsideValues = @@ROWCOUNT410SELECT @ElementIndexOutsideValues = 1411WHILE @ElementIndexOutsideValues <= @ElementCountOutsideValues412BEGIN413  SELECT TOP 1 @SQLOutsideValues = bu.ContentFull414  FROM @ElementBufferOutsideValues bu415  WHERE416    bu.ElementIndex = @ElementIndexOutsideValues417  SELECT @DebugTime = getutcdate()418  INSERT INTO @WerOutsideValues(Parameter1,419  Parameter2,420  Parameter3)421  EXEC sp_executesql @SQLOutsideValues422  IF @DebugSwitch > 0423  BEGIN424    print @SQLOutsideValues print char(9) + 'elapsed ' + str(DATEDIFF(ss, @DebugTime, getutcdate()))425  END426  SELECT @ElementIndexOutsideValues += 1427END428INSERT INTO @ElementBufferMulti(Ident1,429Ident2,430Ident3,431LongIdent1,432Bit1,433UID1,434UID3,435UID2,436LongIdent2,437Int2,438UID4)439SELECT440  tp.TableName AS ParentTable,441  t.TableName AS ChildTable,442  'XObjectKey' AS ParentColumn,443  c.ColumnName AS ChildColumn,444  c.IsPKMember AS IsChildPKMember,445  'D' AS ParentRestriction,446  cc.IS_NULLABLE AS IsChildNullAble,447CASE448  WHEN c.IsPKMember = 1 OR cc.IS_NULLABLE = 'NO' THEN449'Delete'450ELSE 'SetNull'451END AS RepairMethod,452'OutsideAllExistingValues' AS RelationID,4534 AS IsDynamicFK,454'<not available>'455FROM DialogColumn c456  WITH(readpast)457JOIN DialogTable t458  WITH(readpast)459  ON c.UID_DialogTable = t.UID_DialogTable460JOIN @WerOutsideValues w461  ON w.Parameter2 = t.TableName AND w.Parameter3 = c.ColumnName462JOIN DialogTable tp463  WITH(readpast)464  ON tp.TableName = w.Parameter1465JOIN INFORMATION_SCHEMA.COLUMNS cc466  WITH(readpast)467  ON cc.TABLE_NAME = t.TableName AND cc.COLUMN_NAME = c.ColumnName468WHERE469  t.TableName LIKE @ChildTablePattern AND c.ColumnName LIKE @ChildColumnPattern AND(tp.TableName LIKE @ParentTablePattern OR tp.TableName470= '%') AND 'XObjectKey' LIKE @ParentColumnPattern AND(@FilterByModuleGuidAllowed = 0 OR @FilterByModuleGuidAllowed = 1 AND tp.IsModuleGUIDAllowed471= 1) AND NOT(c.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7' AND dbo.QBM_FGIDBOwner() = 'CCC')472SELECT @ElementCount += @@ROWCOUNT473END474SELECT @ElementIndex = 1475WHILE @ElementIndex <= @ElementCount476BEGIN477  SELECT478    TOP 1 @ParentTable = bu.Ident1,479    @ChildTable = bu.Ident2,480    @ParentColumn = bu.Ident3,481    @ChildColumn = bu.LongIdent1,482    @isPKMember = bu.Bit1,483    @ParentRestriction = bu.UID1,484    @isNullableColumn = bu.UID3,485    @RepairMethod = bu.UID2,486    @RelationID = bu.LongIdent2,487    @IsDynamicFK = bu.Int2,488    @ParentTableBase = bu.UID4489  FROM @ElementBufferMulti bu490  WHERE491    bu.ElementIndex = @ElementIndex492  SELECT @cmdwork = replace(CASE @IsDynamicFK493  WHEN 2 THEN494  @CmdCheckDynamicOutOfValid495  WHEN 3 THEN496  @CmdCheckDynamicOutOfTables497  WHEN 4 THEN498  @CmdCheckDynamicOutSide499  ELSE @cmdCheck500  END,501  N '@ChildTable',502  @ChildTable)503  SELECT504    @cmdwork = replace(@cmdwork,505    N '@ParentTable',506    @ParentTable)507  SELECT508    @cmdwork = replace(@cmdwork,509    N '@ChildColumn',510    @ChildColumn)511  SELECT512    @cmdwork = replace(@cmdwork,513    N '@ParentColumn',514    @ParentColumn)515  SELECT516    @cmdwork = replace(@cmdwork,517    N '@RelationID',518    @RelationID)519  SELECT520    @cmdwork = replace(@cmdwork,521    N '%dyn%',522  CASE @IsDynamicFK523    WHEN 1 THEN524  ''525  ELSE '--'526  END)527  SELECT528    @cmdwork = replace(@cmdwork,529    N '%uid%',530  CASE @IsDynamicFK531    WHEN 1 THEN532  '--'533  ELSE ''534  END)535  SELECT536    @cmdwork = replace(@cmdwork,537    N '@OwnerOfParent',538    @OwnerOfParent)539  IF isnull(@ParentTableBase,540  '') <> '<not available>'541  BEGIN542    SELECT543      @cmdwork = replace(@cmdwork,544      N '@ParentBaseTable',545      isnull(@ParentTableBase, @ParentTable))546  END547  ELSE548  BEGIN549    SELECT550      @cmdwork = replace(@cmdwork,551      N '@ParentBaseTable',552      @ParentTable)553  END554  SELECT555    @cmdwork = replace(@cmdwork,556    N '%base%',557  CASE558    WHEN @ParentTable <> isnull(@ParentTableBase, @ParentTable) AND @IsDynamicFK > 0 AND isnull(@ParentTableBase,559  '') <> '<not available>' THEN560  ''561  ELSE '--'562  END)563  SELECT @DebugTime = GETUTCDATE()564  DELETE @RelationswithWrong565  INSERT INTO @RelationswithWrong(Parameter1)566  EXEC sp_executeSQL @cmdwork567  IF @DebugSwitch > 0568  BEGIN569    print @cmdwork print char(9) + 'elapsed ' + str(DATEDIFF(ss, @DebugTime, getutcdate()))570  END571  IF EXISTS(572    SELECT TOP 1 1573    FROM @RelationswithWrong)574  BEGIN575    SELECT @cmdwork = replace(CASE @IsDynamicFK576    WHEN 2 THEN577    @CmdLogDynamicOutOfValid578    WHEN 3 THEN579    @CmdLogDynamicOutOfTables580    WHEN 4 THEN581    @CmdLogDynamicOutSide582    ELSE @CmdLog583    END,584    N '@ChildTable',585    @ChildTable)586    SELECT587      @cmdwork = replace(@cmdwork,588      N '@ParentTable',589      @ParentTable)590    SELECT591      @cmdwork = replace(@cmdwork,592      N '@ChildColumn',593      @ChildColumn)594    SELECT595      @cmdwork = replace(@cmdwork,596      N '@ParentColumn',597      @ParentColumn)598    SELECT599      @cmdwork = replace(@cmdwork,600      N '@IsChildPKMember',601      str(@isPKMember))602    SELECT603      @cmdwork = replace(@cmdwork,604      N '@ParentRestriction',605      @ParentRestriction)606    SELECT607      @cmdwork = replace(@cmdwork,608      N '@IsChildNullable',609      @isNullableColumn)610    SELECT611      @cmdwork = replace(@cmdwork,612      N '@RepairMethod',613      @RepairMethod)614    SELECT615      @cmdwork = replace(@cmdwork,616      N '@RelationID',617      @RelationID)618    SELECT619      @cmdwork = replace(@cmdwork,620      N '%dyn%',621    CASE @IsDynamicFK622      WHEN 1 THEN623    ''624    ELSE '--'625    END)626    SELECT627      @cmdwork = replace(@cmdwork,628      N '%uid%',629    CASE @IsDynamicFK630      WHEN 1 THEN631    '--'632    ELSE ''633    END)634    SELECT635      @cmdwork = replace(@cmdwork,636      N '@OwnerOfParent',637      @OwnerOfParent)638    IF isnull(@ParentTableBase,639    '') <> '<not available>'640    BEGIN641      SELECT642        @cmdwork = replace(@cmdwork,643        N '@ParentBaseTable',644        isnull(@ParentTableBase, @ParentTable))645    END646    ELSE647    BEGIN648      SELECT649        @cmdwork = replace(@cmdwork,650        N '@ParentBaseTable',651        @ParentTable)652    END653    SELECT654      @cmdwork = replace(@cmdwork,655      N '%base%',656    CASE657      WHEN @ParentTable <> isnull(@ParentTableBase, @ParentTable) AND @IsDynamicFK > 0 THEN658    ''659    ELSE '--'660    END)661    SELECT @DebugTime = GETUTCDATE()662    INSERT INTO @erg663    EXEC sp_executesql @cmdwork664    SELECT @CountRows += @@ROWCOUNT665    IF @DebugSwitch > 0666    BEGIN667      print @cmdwork print char(9) + 'elapsed ' + str(DATEDIFF(ss, @DebugTime, getutcdate()))668    END669  END670  SELECT @ElementIndex += 1671END672IF @LogType = 'FULL'673BEGIN674  SELECT675    DISTINCT *676  FROM @erg e677  ORDER BY e.ChildTable,678  e.ChildColumn,679  e.ParentTable,680  e.InvalidValue681  GOTO endLabel682END683IF @LogType = 'REDUCED'684BEGIN685  SELECT686    DISTINCT e.ChildTable AS ChildTablePattern,687    e.ChildColumn AS ChildColumnPattern688  FROM @erg e689  ORDER BY 1,690  2691  GOTO endLabel692END693IF @LogType = 'STORE'694BEGIN695  truncate TABLE QBMForeignKeyWrongReport696  INSERT INTO QBMForeignKeyWrongReport(UID_QBMForeignKeyWrongReport,697  ChildTable,698  ChildColumn,699  ParentTable,700  ParentColumn,701  IsChildPKMember,702  ParentRestriction,703  IsChildNullable,704  RepairMethod,705  RelationID,706  OwnerOfReference,707  InvalidValue,708  XObjectKey)709  SELECT710    UID_QBMForeignKeyWrongReport,711    ChildTable,712    ChildColumn,713    ParentTable,714    ParentColumn,715    IsChildPKMember,716    ParentRestriction,717    IsChildNullable,718    RepairMethod,719    RelationID,720    OwnerOfReference,721    InvalidValue,722    dbo.QBM_FCVElementToObjectKey1('QBMForeignKeyWrongReport',723    'UID_QBMForeignKeyWrongReport',724    x.UID_QBMForeignKeyWrongReport)725  FROM(726  SELECT727    DISTINCT *,728    dbo.QBM_FCVStringToGUID('', e.ChildTable + e.ChildColumn + e.ParentTable + e.ParentColumn + e.RelationID + e.InvalidValue) AS UID_QBMForeignKeyWrongReport729  FROM @erg e) AS x730  ORDER BY x.ChildTable,731  x.ChildColumn,732  x.InvalidValue733  GOTO endLabel734END735END TRY736BEGIN CATCH737  EXEC QBM_PSessionErrorAdd DEFAULT738  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()739  RAISERROR(@Rethrow,740  18,741  1)742    WITH NOWAIT743END CATCH744endLabel: truncate TABLE #TablesForOutside745RETURN(@CountRows)746END
Open raw exported source
SQL · Raw281 lines
1      create   procedure QBM_PForeignKeyWrongReport (@ChildTablePattern varchar(32) = '%' , @ChildColumnPattern varchar(32) = '%' , @ParentTablePattern2 varchar(32) = '%' , @ParentColumnPattern varchar(32) = '%' , @LogType varchar(16) = 'FULL'    , @OwnerOfParent varchar(3) = '%'  , @FilterByModuleGuidAllowed3 bit = 0   , @CheckOutsideValues bit = 0   ) as begin declare @ParentTable nvarchar(64), @ChildTable nvarchar(64), @ParentColumn nvarchar(64), @ChildColumn4 nvarchar(64), @RepairMethod nvarchar(16), @RelationID nvarchar(64), @IsDynamicFK int  , @ParentTableBase varchar(30) declare @ParentRestriction nvarchar5(16) declare @isNullableColumn nvarchar(10) declare @isPKMember bit declare @CmdCheck nvarchar(max) declare @CmdWork nvarchar(max) declare @CmdLog nvarchar6(max) declare @ElementCount int declare @ElementIndex int declare @ElementBufferMulti QBM_YCursorBuffer declare @erg Table ( ChildTable nvarchar(32) collate7 database_default , ChildColumn nvarchar(32) collate database_default , ParentTable nvarchar(32) collate database_default , ParentColumn nvarchar(32) collate8 database_default , IsChildPKMember bit , ParentRestriction nchar(2) collate database_default , IsChildNullable varchar (16) collate database_default ,9 RepairMethod nvarchar (7) collate database_default , RelationID nvarchar (64) collate database_default , OwnerOfReference varchar(3) collate database_default10 , InvalidValue varchar(140) collate database_default ) declare @RelationswithWrong QBM_YParameterList declare @DebugSwitch int = 0 declare @DebugTime 11datetime = getutcdate()  declare @CountRows int = 0 declare @SQLOutsideValues nvarchar(max) declare @WerOutsideValues QBM_YParameterList declare @ElementCountOutsideValues12 int declare @ElementIndexOutsideValues int declare @ElementBufferOutsideValues QBM_YCursorBuffer SET XACT_ABORT OFF BEGIN TRY set nocount on  drop table13 if exists #TablesForOutside  CREATE TABLE #TablesForOutside ( KeyPattern varchar(138) collate database_default not null primary key ) insert into #TablesForOutside14(KeyPattern ) select '<Key><T>' + t.TableName + '</T>' from DialogTable t  exec QBM_PForeignKeyEmptySetNull @ChildTablePattern select @cmdCheck = concat15('16if exists (select top 1 117			from @ChildTable with (readpast)18			where not exists (select top 1 119								from @ParentTable p with (readpast)20								where p.@ParentColumn = @ChildTable.@ChildColumn21							) 22			 and @ChildTable.@ChildColumn > '' ''23%dyn%		 and @ChildTable.@ChildColumn like ''<Key><T>@ParentTable</T>%'' 24'25, case when @OwnerOfParent = '%' then '' else '26%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''27%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''28'29 end, '30			) 31 begin32	select ''@RelationID''33 end34else35 begin36	select '''' where 1 = 0 -- ist nur wegen indirekt37 end38' ) declare @CmdCheckDynamicOutOfValid39 nvarchar(max) = concat('40if exists (select top 1 141			from @ChildTable x with (readpast) left outer join (42												select tp.TableName43												from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn44																			join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable45																			join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable46												where t.TableName = ''@ChildTable''47												 and c.ColumnName = ''@ChildColumn''48												) as y on x.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 49			where y.TableName is null50			 and x.@ChildColumn > '' ''51			 '52, case when @OwnerOfParent = '%' then '' else '53			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''54' end, '55			) 56 begin57	select ''DynamicFK out of''58 end59else60 begin61	select '''' where 1 = 0 -- ist nur wegen indirekt62 end6364'65 ) declare @CmdCheckDynamicOutOfTables nvarchar(max) = concat('6667if exists (select top 1 168			from @ChildTable x with (readpast)69			where x.@ChildColumn > '' ''70			 and not exists ( select top 1 171								from #TablesForOutside a with (forceseek) 72								where left(x.@ChildColumn,  patindex(''%</T>%'', x.@ChildColumn ) + 3 ) =  a.KeyPattern 73							)74			 '75, case when @OwnerOfParent = '%' then '' else '76			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''77' end, '78			) 79 begin80	select ''DynamicFK all''81 end82else83 begin84	select '''' where 1 = 0 -- ist nur wegen indirekt85 end8687'88 ) select @CmdLog = concat('	select 89		 ''@ChildTable''90		, ''@ChildColumn''91		, ''@ParentTable''92		, ''@ParentColumn''93		, @IsChildPKMember94		, ''@ParentRestriction''95		, ''@IsChildNullable''96		, ''@RepairMethod''97		, ''@RelationID''98		, case dbo.QBM_FCVGUIDToModuleOwner(@ChildColumn)99				when ''ZZZ'' then left(@ChildColumn, 3)100				else dbo.QBM_FCVGUIDToModuleOwner(@ChildColumn)101		  end102		, @ChildColumn103					from @ChildTable with (readpast)104		where not exists (select top 1 1105							from @ParentTable p with (readpast)106							where p.@ParentColumn = @ChildTable.@ChildColumn) 107%base%			and not exists (select top 1 1 from @ParentBaseTable b with (readpast) where b.@ParentColumn = @ChildTable.@ChildColumn) 108	-- und der Childschlssel ist nicht leer109				 and @ChildTable.@ChildColumn > '' ''110%dyn%		 and @ChildTable.@ChildColumn like ''<Key><T>@ParentTable</T>%'' 111'112, case when @OwnerOfParent = '%' then '' else '113%dyn%		 and dbo.QBM_FCVObjectKeyToModuleOwner(@ChildTable.@ChildColumn) like ''@OwnerOfParent''114%uid%		 and left(@ChildTable.@ChildColumn, 3) like ''@OwnerOfParent''115'116 end, '117' ) Declare @CmdLogDynamicOutOfValid nvarchar(max) = concat('	select 118		 ''@ChildTable''119		, ''@ChildColumn''120		, dbo.QBM_FCVObjectKeyToElement(''TableName'', x.@ChildColumn)121		, ''XObjectKey''122		, @IsChildPKMember123		, ''@ParentRestriction''124		, ''@IsChildNullable''125		, ''@RepairMethod''126		, ''@RelationID''127		, case dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)128				when ''ZZZ'' then ''CCC''129				else dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)130		  end131		, @ChildColumn132					from @ChildTable x with (readpast) left outer join (133												select tp.TableName134												from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn135																			join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable136																			join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable137												where t.TableName = ''@ChildTable''138												 and c.ColumnName = ''@ChildColumn''139												) as y on x.@ChildColumn like ''<Key><T>'' + y.TableName + ''</T>%'' 140			where y.TableName is null141			and x.@ChildColumn > '' ''142			'143, case when @OwnerOfParent = '%' then '' else '144			and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''145' end, '146' ) Declare147 @CmdLogDynamicOutOfTables nvarchar(max) = concat('	148		select 149		 ''@ChildTable''150		, ''@ChildColumn''151		, dbo.QBM_FCVObjectKeyToElement(''TableName'', x.@ChildColumn)152		, ''XObjectKey''153		, @IsChildPKMember154		, ''@ParentRestriction''155		, ''@IsChildNullable''156		, ''@RepairMethod''157		, ''@RelationID''158		, case dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)159				when ''ZZZ'' then ''CCC''160				else dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)161		  end162		, @ChildColumn163					from @ChildTable x with (readpast)164			where x.@ChildColumn > '' ''165			 and not exists ( select top 1 1166								from #TablesForOutside a with (forceseek) 167								where left(x.@ChildColumn,  patindex(''%</T>%'', x.@ChildColumn ) + 3 ) =  a.KeyPattern 168							)169			 '170, case when @OwnerOfParent = '%' then '' else '171			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''172' end, '173' ) declare174 @CmdCheckDynamicOutSide nvarchar(max) = concat('175if exists (select top 1 1176			from @ChildTable x with (readpast) left outer join @ParentBaseTable p with (readpast) on x.@ChildColumn = p.XObjectKey177				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''178				and p.XObjectKey is null					179				and x.@ChildColumn > '' ''180				'181, case when @OwnerOfParent = '%' then '' else '182				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''183' end, '184			) 185 begin186	select ''DynamicFK outside''187 end188else189 begin190	select '''' where 1 = 0 -- ist nur wegen indirekt191 end192193'194 ) Declare @CmdLogDynamicOutSide nvarchar(max) = concat('	select 195		 ''@ChildTable''196		, ''@ChildColumn''197		, dbo.QBM_FCVObjectKeyToElement(''TableName'', x.@ChildColumn)198		, ''XObjectKey''199		, 0200		, ''@ParentRestriction''201		, ''@IsChildNullable''202		, ''@RepairMethod''203		, ''@RelationID''204		, case dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)205				when ''ZZZ'' then ''CCC''206				else dbo.QBM_FCVObjectKeyToModuleOwner(@ChildColumn)207		  end208		, @ChildColumn209				from @ChildTable x with (readpast) left outer join @ParentBaseTable p with (readpast) on x.@ChildColumn = p.XObjectKey210				where x.@ChildColumn like ''<Key><T>@ParentTable</T><P>%''211				and p.XObjectKey is null					212				and x.@ChildColumn > '' ''213				'214, case when @OwnerOfParent = '%' then '' else '215				and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''216' end, '217' ) delete218  @ElementBufferMulti  insert into @ElementBufferMulti (Ident1  , Ident2  , Ident3  , LongIdent1  , Bit1  , UID1  , UID3  , UID2  , LongIdent2  , Int2 219 , UID4  ) select ParentTable , ChildTable , ParentColumn , ChildColumn , IsChildPKMember , ParentRestriction , IsChildNullable , RepairMethod , RelationID220 , IsDynamicFK , fr.ParenttableBase from QBM_VForeignKeyRepairable fr where fr.ChildTable like @ChildTablePattern and fr.ChildColumn like @ChildColumnPattern221 and (fr.ParentTable like @ParentTablePattern or fr.Parenttable = '%') and fr.ParentColumn like @ParentColumnPattern and (@FilterByModuleGuidAllowed = 2220 or @FilterByModuleGuidAllowed = 1 and fr.IsParentTabModuleGUIDAllowed = 1 ) order by fr.ChildTable,fr.ChildColumn, fr.ParentTable , fr.ParentColumn select223 @ElementCount = @@ROWCOUNT  if @CheckOutsideValues = 1 begin   insert into @ElementBufferOutsideValues (ContentFull) select '224		select distinct t.tablename as tabletarget, '''225 + yt.TableName + ''', ''' + yc.ColumnName + '''226			from ' + yt.TableName + ' x with (readpast) join DialogTable t with (readpast) on x.' + yc.ColumnName227 + ' like ''<Key><T>'' + t.TableName + ''</T><P>%''228			'  from DialogColumn yc with (readpast) join DialogTable yt with (readpast) on yc.UID_DialogTable229 = yt.UID_DialogTable and yt.TableType in ('V', 'T') and yt.UsageType not in ('HISTORY') left outer join DialogValidDynamicRef r with (readpast) on yc.UID_DialogColumn230 = r.UID_DialogColumn where yc.IsDynamicFK = 1 and r.UID_DialogColumn is null and yc.SchemaDataLen = 138 and yc.SchemaDataType = 'varchar' and yc.ColumnName231 <> 'XObjectKey'  and not exists (select top 1 1 from (values ( 'QER-4E75B64F5997406CAC342BAB6A46F8BE')  , ('QER-27E0BCAF583B480499300CFF790725E7')  , 232('QER-9C38234735B84413BE4C612A1A7F7324')  , ('QER-36A10DEFD23E469C8132EFB58032DD85')  , ('HDB-B6167ABE67A74F8DB42E2CBD2D50CE56')  , ('HDB-3935E716EDB64CB587D2ECA6F93ABEA9'233)  , ('QBM-9802EB4D704745458CD18D6850B4909A')  ) as ex (UID_DialogColumn) where ex.UID_DialogColumn = yc.UID_DialogColumn )  and not ( yc.UID_DialogColumn234 = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7'  and dbo.QBM_FGIDBOwner() = 'CCC' )   select @ElementCountOutsideValues = @@ROWCOUNT select @ElementIndexOutsideValues235 = 1 while @ElementIndexOutsideValues <= @ElementCountOutsideValues begin select top 1 @SQLOutsideValues = bu.ContentFull from @ElementBufferOutsideValues236 bu where bu.ElementIndex = @ElementIndexOutsideValues select @DebugTime = getutcdate() insert into @WerOutsideValues(Parameter1, Parameter2, Parameter3237) exec sp_executesql @SQLOutsideValues if @DebugSwitch > 0 begin print @SQLOutsideValues print char(9) + 'elapsed ' + str(DATEDIFF(ss, @DebugTime, getutcdate238()) ) end select @ElementIndexOutsideValues += 1 end  insert into @ElementBufferMulti (Ident1  , Ident2  , Ident3  , LongIdent1  , Bit1  , UID1  , UID3239  , UID2  , LongIdent2  , Int2  , UID4  )  select tp.TableName as ParentTable , t.TableName as ChildTable ,'XObjectKey' as ParentColumn , c.ColumnName 240as ChildColumn , c.IsPKMember as IsChildPKMember , 'D' as ParentRestriction  , cc.IS_NULLABLE as IsChildNullAble , case when c.IsPKMember = 1 or cc.IS_NULLABLE241 = 'NO' then 'Delete' else 'SetNull' end as RepairMethod , 'OutsideAllExistingValues' as RelationID , 4 as IsDynamicFK    , '<not available>' from DialogColumn242 c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join @WerOutsideValues w on w.Parameter2 = t.TableName and243 w.Parameter3 = c.ColumnName join DialogTable tp with (readpast) on tp.TableName = w.Parameter1 join INFORMATION_SCHEMA.COLUMNS cc with (readpast) on cc.TABLE_NAME244 = t.TableName and cc.COLUMN_NAME = c.ColumnName where t.TableName like @ChildTablePattern and c.ColumnName like @ChildColumnPattern and (tp.TableName 245like @ParentTablePattern or tp.TableName = '%') and 'XObjectKey' like @ParentColumnPattern and (@FilterByModuleGuidAllowed = 0 or @FilterByModuleGuidAllowed246 = 1 and tp.IsModuleGUIDAllowed = 1 ) and not ( c.UID_DialogColumn = 'QBM-FAC53888D84E44CFA01B0C043CD53DE7'  and dbo.QBM_FGIDBOwner() = 'CCC' )  select247 @ElementCount += @@ROWCOUNT end   select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @ParentTable = bu.Ident1  , @ChildTable248 = bu.Ident2  , @ParentColumn = bu.Ident3  , @ChildColumn = bu.LongIdent1  , @isPKMember = bu.Bit1  , @ParentRestriction = bu.UID1  , @isNullableColumn249 = bu.UID3  , @RepairMethod = bu.UID2  , @RelationID = bu.LongIdent2  , @IsDynamicFK = bu.Int2  , @ParentTableBase = bu.UID4 from @ElementBufferMulti bu250 where bu.ElementIndex = @ElementIndex select @cmdwork = replace(case @IsDynamicFK when 2 then @CmdCheckDynamicOutOfValid when 3 then @CmdCheckDynamicOutOfTables251 when 4 then @CmdCheckDynamicOutSide else @cmdCheck end, N'@ChildTable', @ChildTable) select @cmdwork = replace(@cmdwork, N'@ParentTable', @ParentTable252) select @cmdwork = replace(@cmdwork, N'@ChildColumn', @ChildColumn) select @cmdwork = replace(@cmdwork, N'@ParentColumn', @ParentColumn) select @cmdwork253 = replace(@cmdwork, N'@RelationID', @RelationID) select @cmdwork = replace(@cmdwork, N'%dyn%', case @IsDynamicFK when 1 then '' else '--' end ) select254 @cmdwork = replace(@cmdwork, N'%uid%', case @IsDynamicFK when 1 then '--' else '' end ) select @cmdwork = replace(@cmdwork, N'@OwnerOfParent', @OwnerOfParent255)  if isnull(@ParentTableBase,'') <> '<not available>' begin select @cmdwork = replace(@cmdwork, N'@ParentBaseTable', isnull(@ParentTableBase, @ParentTable256)) end else begin select @cmdwork = replace(@cmdwork, N'@ParentBaseTable', @ParentTable) end select @cmdwork = replace(@cmdwork, N'%base%', case when @ParentTable257 <> isnull(@ParentTableBase, @ParentTable) and @IsDynamicFK > 0 and isnull(@ParentTableBase, '') <> '<not available>' then '' else '--' end )     select258 @DebugTime = GETUTCDATE() delete  @RelationswithWrong  insert into @RelationswithWrong(Parameter1) exec sp_executeSQL @cmdwork if @DebugSwitch > 0 begin259 print @cmdwork print char(9) + 'elapsed ' + str(DATEDIFF(ss, @DebugTime, getutcdate()) ) end if exists (select top 1 1 from @RelationswithWrong) begin260     select @cmdwork = replace(case @IsDynamicFK when 2 then @CmdLogDynamicOutOfValid when 3 then @CmdLogDynamicOutOfTables when 4 then @CmdLogDynamicOutSide261 else @CmdLog end, N'@ChildTable', @ChildTable) select @cmdwork = replace(@cmdwork, N'@ParentTable', @ParentTable) select @cmdwork = replace(@cmdwork, 262N'@ChildColumn', @ChildColumn) select @cmdwork = replace(@cmdwork, N'@ParentColumn', @ParentColumn) select @cmdwork = replace(@cmdwork, N'@IsChildPKMember'263, str(@isPKMember)) select @cmdwork = replace(@cmdwork, N'@ParentRestriction', @ParentRestriction) select @cmdwork = replace(@cmdwork, N'@IsChildNullable'264, @isNullableColumn) select @cmdwork = replace(@cmdwork, N'@RepairMethod', @RepairMethod) select @cmdwork = replace(@cmdwork, N'@RelationID', @RelationID265) select @cmdwork = replace(@cmdwork, N'%dyn%', case @IsDynamicFK when 1 then '' else '--' end ) select @cmdwork = replace(@cmdwork, N'%uid%', case @IsDynamicFK266 when 1 then '--' else '' end ) select @cmdwork = replace(@cmdwork, N'@OwnerOfParent', @OwnerOfParent)  if isnull(@ParentTableBase,'') <> '<not available>'267 begin select @cmdwork = replace(@cmdwork, N'@ParentBaseTable', isnull(@ParentTableBase, @ParentTable)) end else begin select @cmdwork = replace(@cmdwork268, N'@ParentBaseTable', @ParentTable) end select @cmdwork = replace(@cmdwork, N'%base%', case when @ParentTable <> isnull(@ParentTableBase, @ParentTable269) and @IsDynamicFK > 0 then '' else '--' end )     select @DebugTime = GETUTCDATE() insert into @erg exec sp_executesql @cmdwork select @CountRows += @@ROWCOUNT270 if @DebugSwitch > 0 begin print @cmdwork print char(9) + 'elapsed ' + str(DATEDIFF(ss, @DebugTime, getutcdate()) ) end end select @ElementIndex += 1 end271   if @LogType = 'FULL' begin select distinct * from @erg e order by e.ChildTable, e.ChildColumn, e.ParentTable, e.InvalidValue goto endLabel end if @LogType272 = 'REDUCED' begin select distinct e.ChildTable as ChildTablePattern, e.ChildColumn as ChildColumnPattern from @erg e order by 1,2 goto endLabel end if273 @LogType = 'STORE' begin truncate table QBMForeignKeyWrongReport insert into QBMForeignKeyWrongReport (UID_QBMForeignKeyWrongReport, ChildTable, ChildColumn274 , ParentTable , ParentColumn, IsChildPKMember, ParentRestriction , IsChildNullable , RepairMethod , RelationID , OwnerOfReference , InvalidValue , XObjectKey275 ) select UID_QBMForeignKeyWrongReport, ChildTable, ChildColumn , ParentTable , ParentColumn, IsChildPKMember, ParentRestriction , IsChildNullable , RepairMethod276 , RelationID , OwnerOfReference , InvalidValue , dbo.QBM_FCVElementToObjectKey1('QBMForeignKeyWrongReport', 'UID_QBMForeignKeyWrongReport', x.UID_QBMForeignKeyWrongReport277) from ( select distinct * , dbo.QBM_FCVStringToGUID('', e.ChildTable + e.ChildColumn + e.ParentTable + e.ParentColumn + e.RelationID + e.InvalidValue 278) as UID_QBMForeignKeyWrongReport from @erg e ) as x order by x.ChildTable, x.ChildColumn, x.InvalidValue goto endLabel end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd279 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: truncate table #TablesForOutside280 return (@CountRows) end 281