May 3, 2004 at 9:23 am
Hi,
I need to alter like 100 stored procedurs (changing the table names), is there any way to change by scripts.
If you have any ideas please help me.
Thanks,
May 3, 2004 at 9:34 am
You can script them all out from Enterprise Manager and create one large script which will drop and recreate your stored procedures. If the table name changes are constant you can then easily replace them all by opening the master script in Query Analyzer or Note pad and use the Search/Replace function. Hope this helps.
My hovercraft is full of eels.
May 3, 2004 at 4:54 pm
I made a stored procedure a while ago to do this. Unfortunately I can't post it here. What I did was to take the sp_helptext sql (IN QA execute sp_helptext sp_helptext against the master database) and re-write it so that when it printed the SQL it did a replace of the old word with the new word. I then wrapped that code into a loop that worked against a cursor based on the following query...
SELECT o.name, o.id
FROM sysobjects o
JOIN syscomments c
ON o.id = c.id
WHERE c.text LIKE '%' + @SourceStr + '%'
AND o.xtype IN ('S','FN','TR','P','IF','F','V','TF')
I've had great success using this. I just copy the resulting script from the output window and check it for formatting errors. I then save it to a file and run it where needed.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
May 4, 2004 at 6:50 am
Thanks for your response,
I did follow the sswords idea, it worked for me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply