Filtering and Exporting SQL Stored Procedures with PowerShell

When managing SQL Server stored procedures, there are times when I need to quickly generate scripts for them, usually for backups, migrations, a specific client or functionality, or documentation. In this document, I show how I use T-SQL and PowerShell to filter stored procedures based on naming patterns and export them into individual .sql files. Each file contains the full DROP IF EXISTS and CREATE script which allows for the easy recreation of the stored procedures.

Running the script. The user account executing this script will require permissions to connect to the target database and read from the "sys.procedures" and "sys.sql_modules" system tables. Additionally, the account must have write permissions for the designated output file directory.

The Script

# Define the SQL Server connection string
$connectionString = "Server=SQLServerName;Database=DBName;Integrated Security=True"

 # Define the output folder for stored procedure scripts
$outputFolder = "C:\API_SPs" # Can also be a network UNC
$logFile = "$outputFolder\ErrorLog.txt" # Log file to store errors
$errorsOccurred = $false  # Flag to track if any errors occurred
$scriptsWritten = $false  # Flag to track if any scripts were actually written

# Ensure output folder exists, create it if necessary
if (!(Test-Path $outputFolder))
{
  New-Item -ItemType Directory -Path $outputFolder -Force | Out-Null
}

# Define SQL query to retrieve stored procedure definitions
$query = @"
SELECT 
 p.name AS ProcedureName,
 s.name AS SchemaName,
 'DROP PROCEDURE IF EXISTS [' + s.name + '].[' + p.name + '];' + CHAR(13) + CHAR(10) +
 'GO' + CHAR(13) + CHAR(10) +
 sm.definition + CHAR(13) + CHAR(10) +
 'GO' AS ScriptContent
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id
JOIN sys.sql_modules sm ON p.object_id = sm.object_id
WHERE 
(
 p.name LIKE 'XXX_YYY%' 
 AND p.name NOT IN ('%BACKUP%', '%OLD%')
)
"@

# Function to log errors to a file and display in the console
function Log-Error
{
  param ([string]$message)
  $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
  $logEntry = "$timestamp - ERROR: $message"
  Add-Content -Path $logFile -Value $logEntry  # Append error to log file

  # Print error directly to console
  Write-Host "ERROR: $message" -ForegroundColor Red  

  # Mark that an error has occurred
  $global:errorsOccurred = $true
}

# Initialize SQL connection and execute query
$sqlConnection = $null  # Variable to hold the connection object
$procedures = @{}  # Dictionary to store procedure names and their scripts

try
{
  # Create and open SQL connection
  $sqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
  $sqlConnection.Open()

  # Check if connection is successful
  if ($sqlConnection.State -ne 'Open')
  {
    throw "Failed to open SQL connection."
  }

  # Execute the query
  $sqlCommand = $sqlConnection.CreateCommand()
  $sqlCommand.CommandText = $query
  $reader = $sqlCommand.ExecuteReader()

  # Read query results
  while ($reader.Read())
  {
    # Ensure valid file names by replacing invalid characters
    $procedureName = $reader["ProcedureName"] -replace '[\\\/:*?"<>|]', '_' # replace \\\/:*?"<>| with underscore
    $scriptContent = $reader["ScriptContent"]
    $procedures[$procedureName] = $scriptContent  # Store in PS hashtable/dictionary
  }

  $reader.Close()  # Close the SQL data reader
}
catch
{
  Log-Error "Database error: $_"
}
finally
{
  # Ensure SQL connection is closed to avoid resource leaks
  if ($sqlConnection -ne $null -and $sqlConnection.State -eq 'Open')
  {
    $sqlConnection.Close()
  }
}

# Write stored procedure scripts to files only if there are procedures to save
if ($procedures.Count -gt 0)
{
  foreach ($procedureName in $procedures.Keys) # $procedures is a PS hashtable/dictionary and .Keys retrieves all keys from the hashtable
  {
    try
    {
      $filePath = "$outputFolder\$procedureName.sql"
      # Write the script content to the file using UTF-8 encoding
      [System.IO.File]::WriteAllText($filePath, $procedures[$procedureName], [System.Text.Encoding]::UTF8)
      $scriptsWritten = $true  # Mark that at least one script was successfully written
    }
    catch
    {
      Log-Error "Failed to write file: $filePath - $_"
    }
  }
}

# Final status message
if ($errorsOccurred -and -not $scriptsWritten)
{
    Write-Host "No stored procedure scripts were saved. Errors occurred. See $logFile for details." -ForegroundColor Yellow
}
elseif ($errorsOccurred -and $scriptsWritten)
{
    Write-Host "Stored procedure scripts have been saved to: $outputFolder, but some errors occurred. See $logFile for details." -ForegroundColor Yellow
}
elseif ($scriptsWritten)
{
    Write-Host "Stored procedure scripts have been successfully saved to: $outputFolder" -ForegroundColor Green
}
else
{
    Write-Host "No stored procedures matched the criteria. No scripts were generated." -ForegroundColor Cyan
}

Steps Performed by the Script

1. Connect to SQL Server: The script establishes a connection to the SQL Server using the provided credentials.
2. Query the Database: It executes a SQL query to retrieve a list of stored procedures that match the specified criteria (e.g., names starting with "CP_API").
3. Generate Drop and Create Scripts: For each stored procedure, the script constructs a SQL script containing the DROP PROCEDURE IF EXISTS and CREATE PROCEDURE statements, including the procedure's full definition.
4. Save to Files: The script generates an individual .sql file for each stored procedure and saves it to the designated directory (e.g., C:\SQLScripts).
5. Handle Exceptions: Any issues encountered during the execution (such as permission issues, missing procedures, cannot connect to SQL server) are handled by the script. Error messages are written to ErrorLog.txt.

Conclusion
This PowerShell script provides an automated and efficient way to mass generate DROP and CREATE scripts for stored procedures in SQL Server, saving them as individual files for easy deployment or backup. By customizing the SQL query criteria, you can target specific stored procedures, such as those beginning with a particular prefix or excluding certain procedures from the output.

Using this script, database administrators or developers can quickly export the definitions of stored procedures for version control, migrations, or disaster recovery planning. This solution simplifies the process of managing stored procedure scripts, making it a valuable tool for SQL Server environments.