Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update Stored Procedure on many schema

Posted on 2011-02-17
3
258 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:m3tech
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 34920987
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
 
LVL 2

Author Comment

by:m3tech
ID: 34926501
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
 
LVL 2

Author Closing Comment

by:m3tech
ID: 34928646
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question