0

We are promoting a lot of old stored procedures to production but the are referencing a development box. Is there any easy way to search all the procedures and update them? I tried updateing the defintion field in the sys.sql_modules, and also tried updating the syscomments.text but was unable to. Here is an example of what I am trying to do.

in a stored proc we have the following select * from vmdproduction.test.dbo.table1, I would like to rename the vmdproduction to production in all cases across all stored procedures.

Thanks

Improve this question
2
  • If you're going to do this more than once, you may want to look at one of the third-party SQL development tools out there. I'm most familiar with Red Gate's SQL Prompt Pro (the feature is called Smart Rename), but it looks like ApexSQL has a free add-in. Commented Jun 21, 2012 at 3:00
  • Actually it is true ,Im going to make it dozen time because the amount of SPs we have is more than 400 plus ,, the reason to modifying is the company running database updating. btw, I have search to free tools but seem not helping me much..... Commented Jun 21, 2012 at 3:18

2 Answers 2

Reset to default
2

This is how I would do it... In Management Studio, right-click your database, select Tasks, Generate Scripts. Go through the wizard and be sure to include all stored procedures and to "Script DROP and CREATE" (under advanced options). This will generate a script file which will drop and recreate all the procedures. You can then do a find and replace before you run the script. Be sure to backup the db before running it though!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks for replying, but what im looking for is how to modify inside specific SP ,example I have employee_id exist in 23 SPs and I want to change to emp_id . by the way,the files which generate by SQL server which does not copy the content of SPs ...I would appropriate your help
What version of SQL Server Management Studio are you using?
Im using Sql server 2008 r2 .... thank you so much ... U saved my time to move faster .... I have get All content of stored procedures. one more Question after Doing the replacement in text editor like notpad++ ,what the way to import the new script ... Sorry for this stupid question but I new to SQl server.
The easiest way is to open the script in SQL Server Management Studio, connect to your database (from the menu Query - Connection - Connect. Then select your database from the Available Databases dropdown) and then press "Execute" to run the script. Again... be sure to backup the database before running the script.
1

Using SQL Server Management Studio
To modify a procedure in Management Studio
1.In Object Explorer, connect to an instance of Database Engine and then expand that instance.
2.Expand 'Databases', expand the database in which the procedure belongs, and then expand 'Programmability'.
3.Expand 'Stored Procedures', right-click the procedure to modify, and then click 'Modify'.
4.Modify the text of the stored procedure.
5.To test the syntax, on the 'Query' menu, click 'Parse'.
6.To save the modifications to the procedure definition, on the 'Query' menu, click 'Execute'.
7.To save the updated procedure definition as a Transact-SQL script, on the 'File' menu, click 'Save As'. Accept the file name or replace it with a new name, and then click 'Save'.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.