4

I have multiple databases on multiple servers (SQL Server 2008) with similar schema. I want to execute Stored Procedure on each of them. Right now I have to execute one by one on every server via SQL Server management studio.

Is there any possibility/option in SQL Server Management Studio that I can execute SP just once on all databases.

Sheikh M. Haris
  • 892
  • 1
  • 9
  • 17
  • I did this as a hack using MS Access and a table of connection strings and ADO. – Raj More Feb 18 '14 at 22:21
  • @RajMore .. means you created an application for that? Can you provide me details .. – Sheikh M. Haris Feb 18 '14 at 22:43
  • You could build a batch script or powershell script that goes through a text file of servers and uses SQLCMD.EXE against them. Post if you are interested in a solution. – Nick.Mc Feb 18 '14 at 22:53
  • @ElectricLlama Yes please provide me the details how to do this – Sheikh M. Haris Feb 18 '14 at 23:12
  • I would go with @DavidAtkinson's answer about using a group of Registered Servers in SSMS. If you need something much more complicated (choosing which servers on the fly, etc) then you're going to have to design and build some sort of an application for that. – Raj More Feb 20 '14 at 19:12

4 Answers4

4

You can use a group query to run a script against more than one server. Look here

Then use the sp_MSForEachDB mentioned by @Ram

SteveB
  • 769
  • 4
  • 18
3

Using the sp_MSForEachDB stored procedure you should be able execute on multiple databases of same server.

EXEC sp_msforeachdb " IF '?' NOT IN ('DBs','to','exclude') 
BEGIN 
    EXEC sp_whatever_you_want_to 
END "
Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100
Ram Grandhi
  • 397
  • 10
  • 27
3

There are two ways I can suggest if you want to avoid doing it programmatically.

1) Use Registered Servers in SSMS. Each target database can be created as a Registered Server within a Server Group. You can then right click on the Server Group and select "New Query". This query will execute against all Registered Servers in the Group. This is explained in detail on MSSQLTips.

2) SQL Multi Script is a dedicated tool we developed at Red Gate to satisfy this use case. However, this isn't integrated into SSMS.

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
2

Looking around I'm sure you could write a powershell or batch script to do this but I do not have time to learn, build and test one.

So I'll do it in the language I'm happiest in: SQL and batch script

  1. Paste the below query into SSMS and run it, substituting

    • Your Server List
    • Path to a file containing the script you want to run (i.e. replace YourSQLScript.SQL)
    • Path to a log file (i.e. replace YourOutputLog.TXT)

You might want to alter your script and add SELECT @@SERVERNAME to the start to log the server to your output file

WITH ServerList As (
SELECT 'Server1' ServerName UNION ALL
SELECT 'Server2' UNION ALL
SELECT 'Server3' UNION ALL
SELECT 'Server4' UNION ALL
SELECT 'Server5'
)

SELECT 
'SQLCMD -S ' + ServerName + ' -E ' + ' -i C:\YourSqlScript.SQL -o C:\YourOutputLog.TXT'
From ServerList
UNION ALL
SELECT 'PAUSE'

So in this example, the file C:\YourSqlScript.SQL should probably contain something like:

SELECT @@SERVERNAME
EXEC sp_msforeachdb 'USE [?]; SELECT '?'; EXEC p_YourStoredProcedure;'

(Thanks to RAM for providing this)

(You should definitely test this script in just one database first)

  1. Copy the output and paste into a text file. Save the text file as MyFirstBatchFile.CMD. Double click this file

  2. Check the output file (C:\YourOutputLog.TXT)

This is not going to work first time - I just built it on the fly to show you how it can be done. If/when you get your first error, sit back take a look and see if you can solve it yourself.

If you need to do this regularly then you can have a think about how you want to automate it. For example there is a way to automate getting a list of servers (hint: SQLCMD -L)

If you are going to regularly administer multiple servers you should consider using Powershell.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Here's a good powershell / SQL reference but you'll need to start at the bottom: https://www.simple-talk.com/sql/database-administration/the-posh-dba---sqlpsx-sql-server-powershell-extensions/ – Nick.Mc Feb 19 '14 at 01:28