By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Search All Tables and Replace

P: n/a
I'm looking for a stored procedure (or query) to search an entire
database for a specific string value and replace it with another. I'm
sure I saw an SP for this a while back by someone, but cannot find it
again. The SP took the search string and replace string as parameters
and did the rest. Any ideas where I can find this ?

Bear in mind, the idea is that this can be re-used and run on any
database, so it would have to find all tables and search through those.
Ta

Ryan

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I'm looking for a stored procedure (or query) to search an entire
database for a specific string value and replace it with another. I'm
sure I saw an SP for this a while back by someone, but cannot find it
again. The SP took the search string and replace string as parameters
and did the rest. Any ideas where I can find this ?

Bear in mind, the idea is that this can be re-used and run on any
database, so it would have to find all tables and search through those.
Ta

Ryan


Google found this:

http://groups.google.ch/groups?hl=en...jomn%404ax.com

You should probably read the whole thread - there are some comments and
corrections to the original code in later posts.

Simon
Jul 23 '05 #2

P: n/a
"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I'm looking for a stored procedure (or query) to search an entire
database for a specific string value and replace it with another. I'm
sure I saw an SP for this a while back by someone, but cannot find it
again. The SP took the search string and replace string as parameters
and did the rest. Any ideas where I can find this ?

Bear in mind, the idea is that this can be re-used and run on any
database, so it would have to find all tables and search through those.
Ta

Ryan


You might find this helpful. Here's a UDF that will generate SQL code that,
when executed, will search each character string column (defined for CHARs
and VARCHARs) of sufficient length in each table of the database and
return those columns that have an occurrence of the indicated string.

Say we have the following tables:

CREATE TABLE T1
(
c1 VARCHAR(10) NOT NULL
)

INSERT INTO T1 (c1)
VALUES ('hello')
INSERT INTO T1 (c1)
VALUES ('goodbye')

CREATE TABLE T2
(
c2 VARCHAR(4) NOT NULL
)

INSERT INTO T2 (c2)
VALUES ('1')
INSERT INTO T2 (c2)
VALUES ('2')

CREATE FUNCTION GenerateCodeForStringColumnCheck
(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @code VARCHAR(8000)
SET @code = ''
SELECT @code =
@code +
'SELECT ''"' + TABLE_SCHEMA + '"'' AS table_schema, ''"' +
TABLE_NAME + '"'' AS table_name, ''"' +
COLUMN_NAME + '"'' AS column_name' +
' WHERE EXISTS (SELECT * FROM "' +
TABLE_SCHEMA + '"."' + TABLE_NAME + '" WHERE "' +
COLUMN_NAME + '" = ''' + @string + ''') UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH >= LEN(@string) AND
DATA_TYPE IN ('char', 'varchar') AND
TABLE_SCHEMA = 'dbo'
RETURN(SUBSTRING(@code, 1, LEN(@code) - LEN('UNION ALL ')))
END

DECLARE @code VARCHAR(8000)
DECLARE @searchString VARCHAR(10)
SET @searchString = 'hello'
SET @code = dbo.GenerateCodeForStringColumnCheck(@searchString )
EXEC(@code)

table_schema table_name column_name
"dbo" "T1" "c1"

--
JAG
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.