Global String Search & Replace In All DB Fields

,

Stored procedure to search every field in the database for a string literal.  Run the stored procedure in Query analyser, which will output sql statements to do the search.  The output of these searches will be the update sql, which again should be pasted into a query analyser window.  As the stored procedures do not do any updates directly, this is perfectly safe to run, and the outputted sql can be edited and split into batches if the database is too big.  Excellent for solving the problem of troublesome "£" characters and searching for offensive data.

CREATE PROCEDURE sp_sys_GolaPonyShard
	@StringToFind VARCHAR(20),
	@StringToReplace VARCHAR(20)
AS
-----------------------------------------------------------
--  Paul Widdecombe, 10/02/04
--  paul.widdecombe at connells.co.uk
--  Global Search and Replace Stored procedure
--  Safe to run, as it prints the selects / updates to query analyser
--  rather than running them direct.
--  Syntax - exec sp_sys_GolaPonyShard 'srchstring', 'replstring'
-----------------------------------------------------------

DECLARE @TableName VARCHAR(50)
DECLARE @FieldName VARCHAR(50)
DECLARE @SQLString VARCHAR(150)

DECLARE tables_cursor CURSOR FAST_FORWARD FOR 
select
	o.name as TableName,
	c.name as FieldName
from 
	syscolumns c 
	inner join sysobjects o on c.id = o.id
	inner join systypes t on c.xtype = t.xtype
where
	o.type = 'U' and o.[name] not in ('dtproperties')
	and t.name in ('char', 'text', 'varchar', 'nchar', 'ntext', 'nvarchar')
order by TableName

OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @TableName, @FieldName

PRINT 'declare @RECORDS INT'

WHILE @@FETCH_STATUS = 0
BEGIN
	set @SQLString = 'SELECT @RECORDS = COUNT(*) FROM ' + @TableName + ' WHERE [' + @FieldName + '] like ''%' + @StringToFind + '%'''

	print 'SET @RECORDS = 0'
	print @SQLString
	print 'IF @RECORDS > 1 PRINT ''UPDATE ' + @TableName + ' SET ' + @FieldName + ' = REPLACE(' + @FieldName + ', ''''' + @StringToFind + ''''', ''''' + @StringToReplace + ''''') WHERE ' + @FieldName + ' LIKE ''''%' + @StringToFind + '%'''''''

	FETCH NEXT FROM tables_cursor INTO @TableName, @FieldName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor
GO

Rate

Share

Share

Rate