"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
