dbo.QBM_PCheckSameCase_138

1create   procedure QBM_PCheckSameCase_138 as begin declare @SQLcmd nvarchar(max) declare @cmdwork nvarchar(max) declare @ElementBuffer QBM_YCursorBuffer
2 declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY    insert into @ElementBuffer (Ident1, Ident2, Ident3, ObjectKey1) select
3 rtrim(tr.parenttable), rtrim(tr.childtable), rtrim(tr.ParentColumn) as ParentColumn, rtrim(tr.ChildColumn) as ChildColumn  from QBM_VQBMRelationALL tr
4    join DialogColumn ic with (readpast) on ic.UID_DialogTable = tr.UID_DialogTableParent and ic.UID_DialogColumn = tr.UID_ParentColumn and ic.SchemaDataType
5 like '%char%'  join dialogtable t1 with (readpast) on tr.UID_DialogTableParent = t1.UID_DialogTable and t1.tabletype in ('T', 'B') join QBM_VSchemaColumns
6 isp on isp.TABLE_NAME = tr.parenttable and isp.COLUMN_NAME = tr.ParentColumn join dialogtable t2 with (readpast) on tr.UID_DialogTableChild = t2.UID_DialogTable
7 and t2.tabletype in ('T', 'B') join QBM_VSchemaColumns isc on isc.TABLE_NAME = tr.ChildTable and isc.COLUMN_NAME = tr.ChildColumn where tr.ParentExecuteBy
8 in ('T', 'D') and tr.IsDynamicFK = 1  select @cmdwork = string_agg( concat(convert(nvarchar(max), N'') , '
9select distinct tx.TableName as ParentTable,  '''
10, t.TableName, ''' as ChildTable , ''XObjectKey'' as ParentColumn, ''', c.ColumnName, ''' as ChildColumn
11	from ', t.TableName, ' x with (readpast) join DialogTable tx with (readpast) on x.'
12, c.ColumnName, ' like concat(''<Key><T>'' , tx.TableName , ''</T>%'' )
13	where dbo.QBM_FGIColumnExists(tx.tablename, ''XObjectKey'') = 1
14') , '
15 union all
16 '
17 )  from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where c.IsDynamicFK = 1 and t.TableType
18 in ('B', 'T') and not exists (select top 1 1 from QBM_VQBMRelationALL r where r.IsDynamicFK = 1 and r.UID_DialogTableChild = c.UID_DialogTable and r.UID_ChildColumn
19 = c.UID_DialogColumn )  insert into @ElementBuffer (Ident1, Ident2, Ident3, ObjectKey1) exec sp_executesql @cmdwork  declare @StatementBuffer QBM_YCursorbuffer
20 insert into @StatementBuffer(ContentFull) select CONCAT('
21
22			if exists (select top 1 1
23						 from  ', e.Ident2, ' join ', e.Ident1 , ' p on p.XObjectKey = '
24, e.Ident2, '.', e.ObjectKey1 , '
25						   where ', e.Ident2, '.', e.ObjectKey1 , ' <> p.XobjectKey  collate SQL_Latin1_General_CP1_CS_AS 
26						)
27			 begin
28				update '
29, e.Ident2, ' set  ', e.ObjectKey1 , ' = p.XObjectKey ' , case when dbo.QBM_FGIColumnExistsInSchema(e.Ident2, 'XDateUpdated') = 1 then ', ' + e.Ident2 
30+'.XDateUpdated = getutcdate(), '+ e.Ident2 +'.XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ,'
31				  from  ', e.Ident2, ' join ', e.Ident1
32 , ' p on p.XObjectKey = ', e.Ident2, '.', e.ObjectKey1 , '
33					   where ', e.Ident2, '.', e.ObjectKey1 , ' <> p.XObjectKey  collate SQL_Latin1_General_CP1_CS_AS 
34			 end
35				'
36 ) from @ElementBuffer e order by e.Ident2, e.Ident1   select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount 
37begin select top 1 @SQLcmd = bu.ContentFull from @StatementBuffer bu where bu.ElementIndex = @ElementIndex exec sp_executesql @SQLCmd select @ElementIndex
38 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow,
39 18, 1) WITH NOWAIT END CATCH end 
40