Thursday, November 8, 2018

MSSQL search and replace value in all tables

It's little script for replace value in database


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