
create table #lexemTables (pkColumn varchar(255), lexemColumn varchar(255), tableName varchar(255))
GO
create table #replaces (name varchar(255), fullname varchar(255))
go

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('GeneratorLinkID','GeneratorPath','VCLib.T_GENERATOR_LINK');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('UserColsID','AttributePath','VCLib.T_USER_COLUMNS');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('DictionaryUpdateID','DateUpdatePath','VCLib.T_DICTIONARY_UPDATE');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('DataformExtensionAddattributeID','AttributePath','VCLib.T_DATAFORM_EXTENSION_ADDATTRIBUTE');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('TemplateFieldID','AttrPath','VCLib.T_TEMPLATE_FIELD');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('GenericReportMainItemID','Sorting','VCLib.T_GENERIC_REPORT_MAIN_ITEM');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('DictionaryUpdateID','AttrsPath','VCLib.T_DICTIONARY_UPDATE');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('DictionaryUpdateID','KeyAttrPaths','VCLib.T_DICTIONARY_UPDATE');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('ReportID','Sorting','VCLib.T_Report');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('ReportID','ForcedFetchAttrs','VCLib.T_Report');

insert into #lexemTables(pkColumn , lexemColumn , tableName )
values('TemplateFieldID','CopyAttributes','VCLib.T_TEMPLATE_FIELD');

insert into #replaces (name, fullname)
select EntName, 'As '+ [Schema] + '.' + EntName
from VCLib.T_ENTITIES


declare @tableCursor cursor, @pkColumn varchar(255), @lexemColumn varchar(255), @tableName varchar(255), @command nvarchar(max)
set @tableCursor = cursor for select pkColumn , lexemColumn , tableName from #lexemTables
open @tableCursor
fetch next from @tableCursor into @pkColumn, @lexemColumn, @tableName 
while @@FETCH_STATUS=0
begin
	set @command = '
		declare @plistCursor cursor, @pathCursor cursor, @stepCursor cursor
		declare @plist varchar(max), @plistPk uniqueidentifier
		declare @path varchar(max)
		declare @step varchar(255), @entName varchar(255)
		declare @result varchar(max)
		
		set @plistCursor = cursor for select '+@pkColumn+', '+@lexemColumn+' from '+@tableName+' where '+@lexemColumn+' like ''%As%''
		open @plistCursor
		fetch next from @plistCursor into @plistPk, @plist
		while @@FETCH_STATUS=0
		begin
			set @result = ''''
		
			set @pathCursor = cursor for select Split from fn_Split(@plist, '';'') order by Number
			open @pathCursor
			fetch next from @pathCursor into @path
			while @@FETCH_STATUS=0
			begin
				set @path = replace(replace(@path,''{'',''''),''}'','''')
				set @result = @result + ''{''
		
				set @stepCursor = cursor for select Split from fn_Split(@path, ''/'') order by Number
				open @stepCursor
				fetch next from @stepCursor into @step
				while @@FETCH_STATUS=0
				begin 
					set @step = rtrim(ltrim(@step))
					if left(@step,2) = ''As''
					begin
						set @entName = ltrim(rtrim(SUBSTRING(@step,3,1000)))
						if exists(select * from #replaces where name = @entName)
						begin
							set @step = (select top 1 fullname from #replaces where name = @entName)
						end 				
					end 
				
					set @result = @result + @step + '' / ''
					fetch next from @stepCursor into @step
				end
				close @stepCursor
		
				set @result = substring(@result, 1, len(@result) - 2) + ''};''
		
				fetch next from @pathCursor into @path
			end
			close @pathCursor
		
			set @result = substring(@result, 1, len(@result) - 1)
		
			update '+@tableName+'
			set '+@lexemColumn+' = @result
			where '+@pkColumn+' = @plistPk
		
			fetch next from @plistCursor into @plistPk, @plist
		end
		close @plistCursor'
	exec sp_executesql @command
	fetch next from @tableCursor into @pkColumn, @lexemColumn, @tableName 
end 
close @tableCursor 


drop table #lexemTables
drop table #replaces


