Home Forums SQL Server 7,2000 T-SQL Run same SQL or Stored Proc for multiple databases RE: Run same SQL or Stored Proc for multiple databases

  • I can think of a couple options:

    1) Install SQL Server 2008 client tools, setup a folder and register your servers, then use the multi-script option to open a connection to all instances and run your query.

    2) Install SQL Server 2005/2008 client tools and use SQLCMD and a script file to loop through all servers and run the query.

    3) Install Windows Powershell and use a powershell script to loop through all instances and run the query (using SQLCMD or OSQL).

    4) Use wscript/cscript and SMO

    5) Look at Redgate tools - they have one that allows for running queries on multiple instances.

    6) Other tools like the Redgate tools that allow multi-select queries.

    7) Install SQL Server 2005/2008 and use SSIS to build a package that loops through all instances. This is more of an ETL tool though - but can work very well.

    8) Use DTS to build a package

    There are probably more - but I think that will get you started. Which one you choose will really depend on how often this needs to be done and whether or not it needs to be automated.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs