Knowledge Base

Snippets

20

-- Used this great tool (https://github.com/FabioParigi/NBrightBuyMigrate ) but ran into a problem with images

-- Create a SP to verify the old string you're looking to replace

CREATE PROCEDURE dbo.GIBS_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname

AS

BEGIN TRY

DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE '

SELECT @sqlCommand = @sqlCommand + 'cast([' + COLUMN_NAME + '] as nvarchar(max)) LIKE ''' + @stringToFind + ''' OR '

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = @schema

AND TABLE_NAME = @table

AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar','xml')

SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)

EXEC (@sqlCommand)

PRINT @sqlCommand

END TRY

BEGIN CATCH

PRINT 'There was an error. Check to make sure object exists.'

PRINT error_message()

END CATCH

GO

 

-- Run the SP to search for the string you're looking to replace

GIBS_FindStringInTable '%productimages%', 'dbo', 'NBrightBuy'

GO

 

-- Update the XMLData column with your new string

UPDATE NBrightBuy SET

XMLData = CAST(REPLACE(CAST(XMLData AS NVARCHAR(MAX)), 'productimages','NBStore\images') AS XML)

GO

select * from NBrightBuy

Posted in: SQL Scripts

Comments

There are currently no comments, be the first to post one!

Post Comment

Only registered users may post comments.