CREATE PROCEDURE SP_REPLACEALLTEXT (@TableName varchar(100), @FindText varchar(100), @ReplaceText varchar(100)) AS
DECLARE @SQL nvarchar(4000)
SET @SQL='UPDATE '+@TableName+' SET '
SELECT @FindText=REPLACE(@FindText, '''', ''''''), @ReplaceText=REPLACE(@ReplaceText, '''', '''''')
SELECT @SQL=@SQL+'['+COLUMN_NAME+']=REPLACE(['+COLUMN_NAME+'], '+''''+@FindText+''', '+''''+@ReplaceText+'''), ' FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar','char')
AND '['+TABLE_SCHEMA+'].['+TABLE_NAME+']'=@TableName
AND TABLE_NAME not like '%Sys_%'
IF @@ROWCOUNT > 0 BEGIN
SET @SQL=LEFT(@SQL, LEN(@SQL)-1)
PRINT(@SQL)
EXEC(@SQL)
PRINT @TableName+': done'
END
GO
EXEC SP_MSFOREACHTABLE 'EXEC SP_REPLACEALLTEXT ''?'', '''''''', ''`'''
DROP PROCEDURE SP_REPLACEALLTEXT
This will replace ' with ` in varchar and char columns. So you just need to change the data types, and the search and replace criteria. Also the AND TABLE_NAME not like '%Sys_%' part to exclude the tables u dont want to do.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.