As a database administrator, there may be times when you need to execute a stored procedure against multiple databases in SQL Server. This can be a time-consuming task if you have to connect to each database individually using SQL Server Management Studio (SSMS). In this article, we will explore a solution using PowerShell to quickly execute a stored procedure against multiple databases without the need for manual connections.

The Setup

Let’s assume we have a scenario where we have multiple databases, each representing a different customer. The schema and objects in these databases are the same, but the data is different. We want to execute a stored procedure called “CustomerSales” in each database to get customer-specific results.

To demonstrate this, let’s create a mock database and stored procedure. We will create a table called “SalesOrder” and insert some sample data. Then, we will create the “CustomerSales” stored procedure that retrieves data from the “SalesOrder” table.

CREATE TABLE SalesOrder (
  SaleID INT,
  LineNumber INT,
  SaleDate DATETIME,
  CustomerID INT,
  ProductID INT,
  Qty INT,
  Price NUMERIC(10,4),
  LineTotal NUMERIC(10,4)
)

INSERT INTO SalesOrder (SaleID, LineNumber, SaleDate, CustomerID, ProductID, Qty, Price, LineTotal)
VALUES 
  (10, 1, '6/11/2020', 1, 50, 2, 100, 200),
  (10, 2, '6/11/2020', 1, 51, 20, 50, 1000),
  (20, 1, '6/15/2020', 4, 52, 50, 10, 500),
  (30, 1, '6/15/2020', 6, 53, 10, 60, 600),
  (30, 2, '6/15/2020', 6, 54, 2, 60, 12),
  (30, 3, '6/15/2020', 6, 55, 100, 50, 5000),
  (40, 1, '6/19/2020', 7, 50, 20, 100, 2000),
  (50, 1, '6/25/2020', 1, 50, 40, 100, 4000),
  (50, 2, '6/25/2020', 1, 57, 40, 25, 1000),
  (50, 3, '6/25/2020', 1, 58, 80, 50, 4000)

CREATE PROCEDURE CustomerSales
AS
BEGIN
   SELECT CustomerID,
          SUM(Qty) AS TotalUnits,
          AVG(Price) AS AveragePrice,
          SUM(LineTotal) AS TotalSale
   FROM SalesOrder
   GROUP BY CustomerID
END

Executing the “CustomerSales” stored procedure will give us the following results:

CustomerID   TotalUnits   AveragePrice   TotalSale
1            182          65.000000      10200.0000
4            50           10.000000      500.0000
6            112          56.666666      5720.0000
7            20           100.000000     2000.0000

Using PowerShell to Execute the Stored Procedure

PowerShell provides a convenient way to connect to SQL Server and execute queries. We can use the “Invoke-SqlCmd” cmdlet from the SqlServer module to achieve this. If you don’t have the module installed, you can install it from the PowerShell Gallery.

Here is an example of how we can use PowerShell to execute the “CustomerSales” stored procedure:

$results = Invoke-SqlCmd -ServerInstance localhost -Database YourDatabase -Query "EXEC CustomerSales"

foreach ($sale in $results) {
    Write-Host("Customer: " + $sale.CustomerID + ", Total Sale: $" + $sale.TotalSale)
}

This script connects to the SQL Server instance, specifies the database to run the command against, and executes the “CustomerSales” stored procedure. The results are stored in the “$results” variable, which we can then iterate over and display selectively. In this example, we are outputting the customer ID and total sale amount for each customer.

If you want to save the results to a file, you can use the “Export-Csv” cmdlet. Here is an example:

$results | Select-Object CustomerID, TotalSale | Export-Csv -Path "sales.csv" -NoTypeInformation

This script selects the “CustomerID” and “TotalSale” fields from the results and exports them to a CSV file named “sales.csv”. You can open the file to view the results.

By using PowerShell, you can easily execute stored procedures against multiple databases in SQL Server without the need for manual connections. This approach allows for automation and flexibility, making it a valuable tool for database administrators.

Remember to use variables for most of the parameter values in your scripts, as this will enable you to script the process to work in loops and against multiple systems.

Thank you for reading this article. We hope you found it helpful in your SQL Server tasks.