dbo.QBM_PForeignKeyObjectKeyNoRef

1     create   procedure QBM_PForeignKeyObjectKeyNoRef (@ObjectKeyDialogColumn varchar(138) , @RepairIt bit ) as begin declare @TableName varchar
2(30) declare @columnname varchar(30) declare @MinLen int declare @DebugSwitch int = 0 declare @SQLcmd nvarchar(max) declare @RefTables QBM_YSingleGUID 
3declare @RefTable varchar(30) declare @Results QBM_YCursorBuffer     SET XACT_ABORT OFF BEGIN TRY set nocount on select top 1 @TableName = t.TableName 
4, @columnname = c.ColumnName , @MinLen = case cc.IS_NULLABLE when 'YES' then 0 else 1 end | sign(isnull(c.MinLen, 0)) from DialogColumn c with (readpast
5) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join INFORMATION_SCHEMA.COLUMNS cc with (readpast) on cc.TABLE_NAME = t.TableName
6 and cc.COLUMN_NAME = c.ColumnName where c.XObjectKey = @ObjectKeyDialogColumn if @DebugSwitch > 0 begin print 'verarbeitung ' + @TableName + '.' + @Columnname
7 print '		minlen' + str(@minlen) end select @SQLcmd = '
8		select distinct dbo.QBM_FCVObjectkeyToElement(''TableName'', ' + @columnname + ')
9			from '
10 + @TableName + ' with (readpast)
11			where ' + @columnname + ' > '' ''
12			'  delete  @RefTables  insert into @RefTables(UID_SingleGuid ) exec sp_executeSql
13 @SQLcmd if @DebugSwitch > 0 begin select * from @RefTables end while exists (select top 1 1 from @RefTables ) begin select top 1 @RefTable = UID_SingleGuid
14  from @RefTables if @DebugSwitch > 0 begin print '@RefTable ' + isnull(@RefTable, '<none>') end if exists (select top 1 1 from DialogTable t with (readpast
15) join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable where t.TableName = @RefTable and c.ColumnName = 'XObjectKey'  and t.TableType
16 in ('T', 'V')  ) begin if @RepairIt = 0 begin  select @SQLcmd = '			
17				select ''' + @TableName + ''', ''' + @columnname + ''', ' + str(@MinLen) + ', x.'
18 + @columnname + '
19					from ' + @TableName + ' x with (readpast) left outer join ' + @RefTable + ' y with (readpast) on x.' + @columnname + ' = y.XObjectKey
20					where x.'
21 + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''
22					 and y.XObjectKey is null
23						' end else begin if @MinLen > 0 begin  
24select @SQLcmd = '			
25					delete ' + @TableName + '
26						from ' + @TableName + ' x left outer join ' + @RefTable + ' y  on x.' + @columnname + ' = y.XObjectKey
27						where x.'
28 + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''
29						 and y.XObjectKey is null
30							' end else begin  select @SQLcmd = concat
31('			
32					update ' , @TableName , '
33						set ' , @columnname , ' = null ' , case when dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') =
34 1 and @columnname not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + '''' else
35 '' end , ' from ' , @TableName , ' x left outer join ' , @RefTable , ' y  on x.' , @columnname , ' = y.XObjectKey
36						where x.' , @columnname , ' like ''<Key><T>'' + '''
37 , @RefTable , ''' + ''</T>%''
38						 and y.XObjectKey is null
39							') end end  end else begin  if @RepairIt = 0 begin select @SQLcmd = '			
40				select '''
41 + @TableName + ''', ''' + @columnname + ''', ' + str(@MinLen) + ', x.' + @columnname + '
42					from ' + @TableName + ' x with (readpast)
43					where x.'
44 + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''
45						' end else begin if @MinLen > 0 begin select @SQLcmd = '			
46					delete '
47 + @TableName + '
48						from ' + @TableName + ' x 
49						where x.' + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''
50							' 
51end else begin select @SQLcmd = concat('			
52					update ' , @TableName + '
53						set ' , @columnname , ' = null ' , case when dbo.QBM_FGIColumnExistsInSchema
54(@TableName, 'XDateUpdated') = 1 and @columnname not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name
55(@@procid) + '''' else '' end , ' from ' , @TableName , ' x 
56						where x.' , @columnname , ' like ''<Key><T>'' + ''' , @RefTable , ''' + ''</T>%''
57							'
58) end end  end if @RepairIt = 0 begin if @DebugSwitch > 0 begin print @Sqlcmd end insert into @Results (Ident1 , ident2  , int1  , ObjectKey1 ) exec sp_executeSQL
59 @SQLcmd end else begin if @DebugSwitch > 0 begin print @Sqlcmd end exec sp_executeSQL @SQLcmd end delete  @RefTables where UID_SingleGuid  = @RefTable
60 end  if @RepairIt = 0 begin select Ident1 as TableName , ident2 as ColumnName , int1 as MinLen , ObjectKey1 as ObjectKeyInvalid from @Results end END 
61TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
62 END CATCH ende:  return end 
63