As a Database Administrator (DBA), working with SQL Server stored procedures is a common task. Stored procedures allow you to automate specific tasks by bundling up a query and executing it as a single set of logic. However, the process of creating and executing stored procedures can be time-consuming and repetitive. So, why not automate the automation?

In this article, we will explore how to build a PowerShell tool that allows you to retrieve stored procedures for review and execute them from the command line. This tool not only saves time but also enables you to incorporate these commands into larger automation projects in the future.

Prerequisites

Before we begin, make sure you have the following prerequisites installed:

• SQLSysClrTypes.msi
• SharedManagementObjects.msi
• PowerShellTools.msi

Here is some PowerShell code that downloads and installs each of the required packages:

$files = [ordered]@{
  'SQLSysClrTypes.msi' = 'http://go.microsoft.com/fwlink/?LinkID=239644&clcid=0x409'
  'SharedManagementObjects.msi' = 'http://go.microsoft.com/fwlink/?LinkID=239659&clcid=0x409'
  'PowerShellTools.msi' = 'http://go.microsoft.com/fwlink/?LinkID=239656&clcid=0x409'
}

foreach ($file in $files.GetEnumerator())
{
  $downloadFile = (Join-Path -Path $env:TEMP -ChildPath $file.Key)
  if (-not (Test-Path -Path $downloadFile -PathType Leaf))
  {
    Invoke-WebRequest -Uri $file.Value -OutFile $downloadFile
  }
  Start-Process -FilePath 'msiexec.exe' -Args "/i $downloadFile /qn" -Wait
}

Building the PowerShell Tool

To build an effective PowerShell tool, it’s important to make the code as generic as possible and create input parameters that encourage reuse. This means identifying items that might change over time and building them as parameters rather than static references.

Let’s start by creating a function to run a stored procedure:

function Invoke-SqlStoredProcedure
{
  [CmdletBinding()]
  param
  (
    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$ServerName,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$Database,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$Name,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [pscredential]$Credential
  )

  begin
  {
    $ErrorActionPreference = 'Stop'
  }

  process
  {
    try
    {
      $connectionString = New-SqlConnectionString -ServerName $ServerName -Database $Database -Credential $Credential
      $SqlConnection = New-SqlConnection -ConnectionString $connectionString

      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
      $SqlCmd.CommandText = $Name
      $SqlCmd.Connection = $SqlConnection

      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $SqlAdapter.SelectCommand = $SqlCmd

      $DataSet = New-Object System.Data.DataSet
      $SqlAdapter.Fill($DataSet)
    }
    catch
    {
      $PSCmdlet.ThrowTerminatingError($_)
    }
  }
}

Most of the code for this function is available with SQL Server Management Objects (SMO) when you install the SQLPS module. However, notice the references to New-SqlConnectionString and New-SqlConnection. These are custom functions that we have broken out for reusability.

Next, let’s create a function to list all the stored procedures on a SQL server:

function Get-SqlStoredProcedure
{
  [OutputType([Microsoft.SqlServer.Management.Smo.StoredProcedure])]
  [CmdletBinding()]
  param
  (
    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$ServerName,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$Database,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [pscredential]$Credential
  )

  begin
  {
    $ErrorActionPreference = 'Stop'
  }

  process
  {
    try
    {
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

      $connectionString = New-SqlConnectionString -ServerName $ServerName -Database $Database -Credential $Credential
      $sqlConnection = New-SqlConnection -ConnectionString $connectionString

      $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlConnection

      $serverInstance.Databases[$Database].StoredProcedures
    }
    catch
    {
      $PSCmdlet.ThrowTerminatingError($_)
    }
  }
}

Notice that we can refer to the shared functions New-SqlConnectionString and New-SqlConnection since making the initial connection is a common attribute for both functions. This is a best practice when building a tool like this.

Testing the Tool

Now, let’s test our tool by listing all the stored procedures in a SQL database and executing a specific stored procedure.

For example, to query all the stored procedures in the “myazuredatabase” database on the server “adbsql.database.windows.net”, run the following command:

Get-SqlStoredProcedure -ServerName adbsql.database.windows.net -Database myazuredatabase -Credential (Get-Credential)

After providing your SQL username and password, you should see a list of stored procedures and their properties.

To execute a specific stored procedure, such as “dbo.uspLogError”, run the following command:

Invoke-SqlStoredProcedure -ServerName adbsql.database.windows.net -Database myazuredatabase -Name dbo.uspLogError -Credential (Get-Credential)

After providing your username and password again, you will see the result of the stored procedure.

By building a tool in PowerShell, you can save a significant amount of time. Whether you use it for ad-hoc queries or incorporate it into larger automation processes, this tool will help you focus on more important tasks.