Search and Replace in Stored procs? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Search and Replace in Stored procs?

Author

Topic

jhilb007
Starting Member

17 Posts

Posted - 2003-05-23 : 15:22:43

Anyone have a script to do a textual search and replace for stored procs? I know I can script out the procs and do it, but that is not really what I want to do. I want to just be able to run something to do it.

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-23 : 15:43:54

You can try using the syscomments table to do this. You may find more problems for yourself though than benefits.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-23 : 15:58:26

I would not recommend updating syscomments to do this. Scripting it out is what I recommend even though you say that it's not what you want to do.

Tara

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-23 : 16:06:07

I meant more puling the text from syscomments, manipulating the code and resubmitting. Updating the Syscomments table is not an option.

--Something kind of like this
SELECT @SQL = Text from Syscomments

SET @SQL = REPLACE(@SQL, 'Search Expression', 'New Expression')
SET @SQL = REPLACE(@SQL, 'Create Procedure', 'Alter Procedure')

EXEC(@SQL)

But like I said, There are lots of hurdles and problems with this. You could do it for some circumstances though. The recommendation is to generate a script and update it.

I am sorry I was unclear before.

jhilb007
Starting Member

17 Posts

Posted - 2003-05-23 : 16:29:26

Actually, that is what I am doing. The problem I am running into is when the text of the proc is over 8000 chars.

What I am doing is.

1. Grabbing the old proc via syscomments
2. dropping the proc
3. exec (@proc) to create the new proc (after doing my replace)

it works great for procs no longer than 8000 chars, but on those greater than 8000 chars, problems.

can't use ntext or text as a mem var?

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-23 : 16:46:24

Here are some options for that.

1) Write the 8000 char blocks out to file. Use Isql to exec script

2) Write the blocks to a temp table text datatype. Make the substitutions. Use multiple variables to exec the code
EXEC(@Sql1 + @Sql2+ @Sql3)

I want to reiterate that you might make yourself quite unhappy by going down this path.

Consider this script as an example of the sort of problem you may run into. I am sure a smarter fellow might be able to come up with an example more on point, but hopefully you get what I am getting at.

CREATE TABLE Test1(
Col1 INT)

INSERT Test1 VALUES (1)
GO
CREATE View GetTest1
AS
SELECT * FROM Test1
GO
SELECT * FROM GetTest1
ALTER TABLE Test1 ADD Col2 int IDENTITY(1,1)

--Notice here that Only Col1 is in the result set
SELECT * FROM GetTest1

GO
ALTER VIEW GetTest1
AS
SELECT * FROM Test1
GO
--Now column 1 and Column 2 are in the result set
SELECT * FROM GetTest1

-- DROP TABLE Test1
-- DROP View GetTest1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources