Update Stored Procedure on many schema

I need to updated a stored procedure in each of roughly 1000 schema distributed across 10 databases in MS SQL 2005.

I have the alter procedure t-sql to update one schema. To apply the change to a different schema the procedure name and several calls to other procedures have to be updated with the correct schema. I'm currently doing this with a find and replace from the management studio while testing.

Is there a way to efficiently do this across many schema without manually updating the t-sql each time? If so, please give detail.
LVL 2
m3techAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
I think you can use SQLCMD for this task.  It provides a way to script T-SQL.

More details here: http://msdn.microsoft.com/en-us/library/ms188714.aspx

Something like this:

:setvar SQLCMDLOGINTIMEOUT 60
:setvar server "testserver"
:setvar MYDATABASE AdventureWorks2008R2
:setvar ProcName MyProducedureName

:connect $(server) -l $(SQLCMDLOGINTIMEOUT)

USE $(MYDATABASE); 

ALTER PROCEDURE [dbo].[$(ProcName)]
AS
BEGIN
   ....
END

Open in new window

0
 
m3techAuthor Commented:
Thanks, that looks like it will do the trick. I'll give it a shot with an addtional variable for the procedure's schema.

I was already considering modifying a script file with another scripting language and feeding it to sqlcmd. Feeding different variables to sqlcmd will definitely be easier.
0
 
m3techAuthor Commented:
I specifically asked about varying schema and the solution provided did not account for this. However, the change needed was minor and easy to make.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.