Find and Replace in all stored procedures

Question

Post reply

Find and Replace in all stored procedures

garethmann101

SSCommitted

Points: 1980
More actions
November 25, 2008 at 1:09 pm

#196634

Is there a way to do a 'find and replace' in all stored procedures of a given database? If not then is there at least a way to do a find, so that i can go in and edit by hand each instance of a given text?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

You can use this to find stored procedures that contain the given search criteria.

select name, text from sys.objects A

JOIN sys.syscomments B ON A.object_id = B.id

where type = 'P' and text like '%search here%'

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can script out all your sp's and include the files in an SSMS project and then do Find and Replace against the scripts.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

I think that this should do it:

declare @sql nvarchar(MAX);

Set @sql = N'

declare @sql nvarchar(MAX);

Set @sql = N'''';

';

Select

@sql = @sql

+ 'Select

@sql = Replace(

Replace(definition, N''search text'', N''replace text'')

, N''CREATE PROC'', N''ALTER PROC'')

+ N''

'' From sys.sql_modules M

Where M.object_id = '+M.object_id+';

EXEC (@sql);

'

From sys.sql_modules M

Join sys.objects O on O.object_id = M.object_id

Where O.Type = 'P';

EXEC @sql;

Untested, use at your own risk...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Oh, and "No Cursors"!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

krayknot SSCertifiable Points: 7998 More actions · Answer 5

garethmann101 (11/25/2008)
Is there a way to do a 'find and replace' in all stored procedures of a given database? If not then is there at least a way to do a find, so that i can go in and edit by hand each instance of a given text?

jack corbett's idea is perfect. script out all the sps and then do find and replace. and at last replace the Create procedure with Alter procedure and run the script

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Of course, that is exactly what my script does for you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

rbarryyoung (11/25/2008)
Of course, that is exactly what my script does for you.

Yup, that is correct. Of course shouldn't you already have a project with the scripts in Source Control?:D

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jack Corbett (11/26/2008)
rbarryyoung (11/25/2008)
Of course, that is exactly what my script does for you.
Yup, that is correct. Of course shouldn't you already have a project with the scripts in Source Control?:D

Heh. Of course there is a big difference between "should have" and "have". 🙂 Ideally(*), this should be the case, but in practice, I hardly ever see it.

(*: and "ideally" is arguable: the traditional developer's model of source code control is not necessarily practical for all situations).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

rbarryyoung (11/26/2008)
Jack Corbett (11/26/2008)
rbarryyoung (11/25/2008)
Of course, that is exactly what my script does for you.
Yup, that is correct. Of course shouldn't you already have a project with the scripts in Source Control?:D
Heh. Of course there is a big difference between "should have" and "have". 🙂 Ideally(*), this should be the case, but in practice, I hardly ever see it.
(*: and "ideally" is arguable: the traditional developer's model of source code control is not necessarily practical for all situations).

I have to admit that I haven't done it, but I think I will.:P The hardest part of that with SQL Server (if you aren't able to use VS Database edition or TFS) is that you can make changes OUTSIDE the source control solution. You really need to have some discipline.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sailor SSCertifiable Points: 5246 More actions · Answer 10

I'm on SQL 2008. I am trying the script and getting an error:

Msg 245, Level 16, State 1, Line 7

Conversion failed when converting the varchar value ';

EXEC (@sql);

' to data type int.

Sailor SSCertifiable Points: 5246 More actions · Answer 11

I have changed

m.objectid to

cast( M.object_id as varchar)