Replacing text in multiple stored procedures

Question

Post reply

Replacing text in multiple stored procedures

chelta

SSC-Addicted

Points: 429
More actions
April 20, 2010 at 11:28 pm

#144235

Hi,
I have a requirement where I need to replace an email address in multiple stored procedures to a new one. Instead of sorting through all the stored procedures, altering it manually then executing the script to update each one - I would like to be able to script it out, so it searches through all the stored procedures in a database and updates them accordingly. Has anyone done this? I've tried previously manually updating the sys.syscomments table, but obviously I could not do this.
Help anyone? 😉

Viewing 5 posts - 1 through 4 (of 4 total)

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

Dhananjay-440114 Ten Centuries Points: 1365 More actions · Answer 1

you can run update query on sys.comments table in sql 2005 or syscomments in sql 2000. it may solve uor problem.

cheers

chelta SSC-Addicted Points: 429 More actions · Answer 2

Hi,

If you mean using 'sys.syscomments' in SQL2005 - I have attempted to perform an update to the text field, however the result is:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

I believe that this is by design. Is there a way to replace the text I need to, and at the same time create an alter version of the procedure and execute it? I think that might be the way around it?

Dhananjay-440114 Ten Centuries Points: 1365 More actions · Answer 3

You need to allow updates in system table by executing below command.

sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

cheers!!!

Lowell SSC Guru Points: 323518 More actions · Answer 4

in 2005 and above, syscomments is a view, and no matter the override setting, cannot be updated.

please test any advice you make before posting it...it can mislead floks who don't know!

i've done something kind of similar to the OP requirement, and it was all dynamic sql:

selecting the text from sql_modules into a variable, adding a header and doing a replace(CREATE-->ALTER), then executing the string, all in a <gasp!> cursor.

here is a working example

declare

@header nvarchar(max),

@objname nvarchar(max),

@cmd nvarchar(max)

declare c1 cursor for

select

obs.name,

--SCHEMA_NAME(obs.schema_id),

mods.definition from sys.objects obs

inner join sys.sql_modules mods on obs.object_id = mods.object_id

where obs.is_ms_shipped = 0

AND mods.definition NOT LIKE '%--#########%' --skip if already "headerized"

AND obs.type_desc IN('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')

open c1

fetch next from c1 into @objname,@cmd

While @@fetch_status <> -1

begin

SET @header = convert(nvarchar(max),N'-- HEADER: <yourProcName>

--#############################################################################

-- updated : ' + CONVERT(nvarchar(50),GETDATE(),121) + '

-- server: ' + db_name() + '

--#############################################################################

--if you are going to put this in MASTER, and want it to be able to query

--each database''s sys.objects.columns/indexes, you MUST mark it as a system procedure:

--EXECUTE sp_ms_marksystemobject ''<yourProcName>''

--#############################################################################

')

SET @header = REPLACE(@header,N'<yourProcName>',@objname)

SET @cmd = @header + REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))

print @cmd

exec (@cmd)

fetch next from c1 into @objname,@cmd

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!