November 25, 2008 at 1:09 pm
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?
November 25, 2008 at 6:30 pm
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%'
November 25, 2008 at 8:09 pm
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
November 25, 2008 at 9:26 pm
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]
November 25, 2008 at 9:26 pm
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]
November 25, 2008 at 9:35 pm
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
November 25, 2008 at 9:48 pm
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]
November 26, 2008 at 7:11 am
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
November 26, 2008 at 7:35 am
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]
November 26, 2008 at 7:46 am
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
September 28, 2012 at 7:00 am
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.
September 28, 2012 at 7:43 am
I have changed
m.objectid to
cast( M.object_id as varchar)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply