Post reply

Executing T-SQL to multiple databases with Maintenance Plan??

  • September 3, 2008 at 9:32 pm

    #190100

    I have a maintenance plan which goes through selected databases doing index rebuild etc.

    Now I want to set up a "clean up" stored procedure to each customer db (to delete outdated rows) and call that SP from Maintenance Plan

    How do I set up Maintenance Plan to execute Stored Procedure on each target database without hard-coding the database names?

    I believe that right now I could run those SP:s by creating a "Execute T-SQL statement task" with

    EXECUTE Client1DB.dbo.DoStuff

    EXECUTE Client2DB.dbo.DoStuff

    EXECUTE Client3DB.dbo.DoStuff

    EXECUTE Client4DB.dbo.DoStuff

    but this is a pain because it requires changes every time we get a new client db.

  • September 4, 2008 at 2:49 am

    #866997

    The fact that you say that the maintenance plan only does things for certain databases makes it difficult to make it dynamic. But if you have some common way of identifying the databases you might be able to use the sp_MSForeachdb stored procedure.

    For example to execute some code on all databases with a name starting with UDB

    Exec sp_MSForeachdb

    'USE [?]IF DB_ID(''?'') LIKE 'UDB%"

    DO Something here'

    [font="Verdana"]Markus Bohse[/font]

  • September 4, 2008 at 3:03 am

    #867001

    Or you could have a job that goes through a list of databases and does what you want.

    "Keep Trying"

Viewing 3 posts - 1 through 2 (of 2 total)

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