dbo.QBM_PForeignKeyWrongReport

1      create   procedure QBM_PForeignKeyWrongReport (@ChildTablePattern varchar(32) = '%' , @ChildColumnPattern varchar(32) = '%' , @ParentTablePattern
2 varchar(32) = '%' , @ParentColumnPattern varchar(32) = '%' , @LogType varchar(16) = 'FULL'    , @OwnerOfParent varchar(3) = '%'  , @FilterByModuleGuidAllowed
3 bit = 0   , @CheckOutsideValues bit = 0   ) as begin declare @ParentTable nvarchar(64), @ChildTable nvarchar(64), @ParentColumn nvarchar(64), @ChildColumn
4 nvarchar(64), @RepairMethod nvarchar(16), @RelationID nvarchar(64), @IsDynamicFK int  , @ParentTableBase varchar(30) declare @ParentRestriction nvarchar
5(16) declare @isNullableColumn nvarchar(10) declare @isPKMember bit declare @CmdCheck nvarchar(max) declare @CmdWork nvarchar(max) declare @CmdLog nvarchar
6(max) declare @ElementCount int declare @ElementIndex int declare @ElementBufferMulti QBM_YCursorBuffer declare @erg Table ( ChildTable nvarchar(32) collate
7 database_default , ChildColumn nvarchar(32) collate database_default , ParentTable nvarchar(32) collate database_default , ParentColumn nvarchar(32) collate
8 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_default
10 , 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 @ElementCountOutsideValues
12 int declare @ElementIndexOutsideValues int declare @ElementBufferOutsideValues QBM_YCursorBuffer SET XACT_ABORT OFF BEGIN TRY set nocount on  drop table
13 if exists #TablesForOutside  CREATE TABLE #TablesForOutside ( KeyPattern varchar(138) collate database_default not null primary key ) insert into #TablesForOutside
14(KeyPattern ) select '<Key><T>' + t.TableName + '</T>' from DialogTable t  exec QBM_PForeignKeyEmptySetNull @ChildTablePattern select @cmdCheck = concat
15('
16if exists (select top 1 1
17			from @ChildTable with (readpast)
18			where not exists (select top 1 1
19								from @ParentTable p with (readpast)
20								where p.@ParentColumn = @ChildTable.@ChildColumn
21							) 
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 begin
32	select ''@RelationID''
33 end
34else
35 begin
36	select '''' where 1 = 0 -- ist nur wegen indirekt
37 end
38' ) declare @CmdCheckDynamicOutOfValid
39 nvarchar(max) = concat('
40if exists (select top 1 1
41			from @ChildTable x with (readpast) left outer join (
42												select tp.TableName
43												from DialogValidDynamicRef vr with (readpast) join DialogColumn c with (readpast) on vr.UID_DialogColumn = c.UID_DialogColumn
44																			join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable
45																			join DialogTable tp with (readpast) on vr.UID_DialogTableReference = tp.UID_DialogTable
46												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 null
50			 and x.@ChildColumn > '' ''
51			 '
52, case when @OwnerOfParent = '%' then '' else '
53			 and dbo.QBM_FCVObjectKeyToModuleOwner(x.@ChildColumn) like ''@OwnerOfParent''
54' end, '
55			) 
56 begin
57	select ''DynamicFK out of''
58 end
59else
60 begin
61	select '''' where 1 = 0 -- ist nur wegen indirekt
62 end
63
64'
65 ) declare @CmdCheckDynamicOutOfTables nvarchar(max) = concat('
66
67if exists (select top 1 1
68			from @ChildTable x with (readpast)
69			where x.@ChildColumn > '' ''
70			 and not exists ( select top 1 1
71								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 begin
80	select ''DynamicFK all''