Iterate Sprocs replacing a string

beelineuk
beelineuk used Ask the Experts™
on
How do,

I would like to write a DTS or some sql that will iterate through each proc in the database, find and replace a string, and then save the sproc out again using ALTER.

This is a big job that needs to happen in a short space of time so looking to automate the process, rather than outputting a script from all the sprocs, finding and replacing in a text editor and then running.

Question is how do I read in the sproc contents as a string? I've been poking around and know it can be done, but can't finding the missing part of the puzzle. Can anyone shed some light?

Sorry down to my last points at the moment.

Cheers,

Mike
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you haven't already, I would suggest cross-posting this in the SQL Server section...

Author

Commented:
Oh sorry posted in wrong place :) Meant to post this in the SQL Server section.

How do I move it or cancel the question so that I can repost it somewhere else.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

No, not really the wrong place, just you may have more responses being in both places. I think you can post a 0 point question that contains a link to this posting...

Also, you can post a question about moving & cancelling to the moderators in the community support section:

http://www.experts-exchange.com/Community_Support/

Author

Commented:
Thanks for the info I've requested the question is closed.

It is the wrong place, as I was trying to post under SQL Server. This question has nothing to do with ASP. I'm trying to retrieve the sproc script from within SQL, not from outside.

Author

Commented:
Right now we are on the right forum, I have answered this partly myself.

I've figured I can use sp_OACreate to create an instance of SQLDMO.SQLServer, and then use sp_OAMethod to output the sproc to a file. However there isn't must help on the methods available, and I want to know how to poke the output into a SQL variable instead of a file?


set @method = 'Databases("myDB").StoredProcedures("mySproc").Script(74077, "myFilename.txt", 2)'
exec @hc = sp_OAMethod @obj, @method, @output output

What do I use to put the sproc into output instead of the file?

set @method = 'Databases("myDB").StoredProcedures("mySproc").Script()' ??
Commented:
I just posted to a similar question: You will have to alter the code a bit, but I think it will work.


DECLARE SOMECURSOR CURSOR FOR SELECT NAME, ID from sysobjects WHERE AND xtype = 'P' and base_schema_ver = 0
OPEN SOMECURSOR

DECLARE @NAME       VARCHAR(500)
DECLARE @ID            INT
DECLARE @TEXT       VARCHAR (5000)

FETCH NEXT FROM SOMECURSOR INTO @NAME, @ID

WHILE @@FETCH_STATUS = 0
      BEGIN
            DECLARE SPCURSOR CURSOR FOR  SELECT TEXT from syscomments WHERE ID = @ID
            OPEN SPCURSOR
            
            FETCH NEXT FROM SPCURSOR INTO @TEXT      

            DECLARE @FINALTEXT VARCHAR(5000)
            SELECT @FINALTEXT = ''

                  WHILE @@FETCH_STATUS = 0
                  BEGIN
                        SET @FINALTEXT      = @FINALTEXT + @TEXT                  
                        FETCH NEXT FROM SPCURSOR INTO @TEXT      
                  END
            DROP PROCEDURE @NAME
            SET @FINALTEXT = REPLACE(@FINALTEXT, 'FIND STRING', 'REPLACE STRING')
            EXECUTE @FINALTEXT

            CLOSE SPCURSOR
            DEALLOCATE SPCURSOR            

            FETCH NEXT FROM SOMECURSOR INTO @NAME, @ID
      END



CLOSE SOMECURSOR
DEALLOCATE SOMECURSOR

Author

Commented:
Well I did ask for this to be closed as I found my own way of doing this using SQLDMO.SQLServer, however looking at your code sample there is a much simpiler solution. What your saying is that the sproc contents are actually a Text field in syscomments, which is all I wanted to know. I can read this manuipluate it how I want and split it out again.

Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial