Post reply

How do I update a stored procedure in multiple databases?

  • April 21, 2005 at 4:58 am

    #165282

    I have an application that runs several stored procedures on client data.  Each client's data is put into a separate database and each client database has the same tables and the same set of functions and stored procedures.  If we want to make an update to a stored procedure (or sometimes a table), we currently do this manually using query analyzer, by running the same change script multiple times and changing the USE dbname at the start.

    This is far from ideal as it is prone to errors; a database might get missed.  I know we could write something ourselves to update all of the databases, but I was wondering if there is a tool already available that would make this easier for us?

    Thanks.

  • April 21, 2005 at 5:20 am

    #554033

    you should script this picking up the database names from sysdatabases ( generally where dbid>4 )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • April 21, 2005 at 5:24 am

    #554037

    What you could do is research on this site the use of msforeachDB and use that to loop.  You should be able to find decent SAMPLEs using the search feature here



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • April 21, 2005 at 7:06 am

    #554062

    Thanks for the responses.  I'm not sure that msforeachDB will work, because as well as the client specific databases, there are other databases which contain shared data, system stuff etc which contain different tables.  I need to be able to specify which databases get updated.   

  • April 21, 2005 at 3:07 pm

    #554222

    If you need to pick and choose which databases need updating create a table that contains DB names and LOOP through that....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • April 22, 2005 at 8:57 am

    #554364

    If the stored procedures are the same within each database, you could create just one copy of each procedure in the master database, mark these master stored procedures as system objects (i.e. EXEC sp_ms_marksystemobject sp_Name).

    The same procedures can then be run in multiple databases, but referencing the database it's called from (e.g. Northwind..sp_Name)

    Might help...

  • April 25, 2005 at 4:23 am

    #554534

    Thanks rsnorris, that is an excellent idea, we will investigate this further.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply