declare @from as nvarchar(50);
declare @to as nvarchar(50);
set @from =[dbo].[getstringUUIDFromString] ('c3a5fb3e-745a-11e8-a209-0050568411f7');
set @to =[dbo].[getstringUUIDFromString] ('c3a5fb3e-745a-11e8-a209-0050568411f7');
DECLARE @CURSOR CURSOR;DECLARE @script AS NVARCHAR(MAX);
SET @CURSOR = CURSOR SCROLL
FOR
SELECT
'
if exists (select top 1 * from ['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+'] where '+isc.COLUMN_NAME + ' in ('+@from+'))
begin
print ''['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+']'';
update ['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+']
set '+isc.COLUMN_NAME + ' = '+@to+'
where '+isc.COLUMN_NAME + ' in ('+@from+')
end;
'
FROM information_schema.columns isc
where TABLE_CATALOG='DiachokERP' and TABLE_SCHEMA='dbo'
and data_type = 'binary' and CHARACTER_MAXIMUM_LENGTH=16
OPEN @CURSOR
FETCH NEXT
FROM @CURSOR
INTO @script
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@script);
--print @script
FETCH NEXT
FROM @CURSOR
INTO @script
END
CLOSE @CURSOR
DEALLOCATE @CURSOR;
and converting string from uid to uuid
CREATE function [dbo].[getstringUUIDFromString](@stringUUID as varchar(50))
returns nvarchar(50)
as
begin
--Возврат Сред(GUID, 20, 4) + Прав(GUID, 12) + Сред(GUID, 15, 4) + Сред(GUID, 10, 4) + Лев(GUID, 8);
declare @buffer nvarchar(50)
select @buffer ='0x'+ substring(@stringUUID,20,4)+right(@stringUUID,12)+substring(@stringUUID,15,4)+substring(@stringUUID,10,4)+left(@stringUUID,8)
return @buffer
end
GO
No comments:
Post a Comment