Stored Procedure Generation Script

# Load SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

# Define SQL Server connection details
$serverName = ""          # Replace with your SQL Server name
$databaseName = ""      # Replace with your database name
$rootOutputFolder = "F:SQLScripts"  #Replace with your desired output folder
$outputFolder =  "$rootOutputFolder$serverName$databaseName"      #Creating Subfolder with Database Name

# Create the output directory if it doesn't exist
if (-not (Test-Path -Path $outputFolder)) {
    New-Item -ItemType Directory -Path $outputFolder | Out-Null
}

try {
    # Connect to the SQL Server
    Write-Host "Connecting to SQL Server..." -ForegroundColor Cyan
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
    $database = $server.Databases[$databaseName]

    # Check if the database exists
    if ($null -eq $database) {
        Write-Error "Database '$databaseName' not found on server '$serverName'."
        exit
    }

    # Scripter object and options
    $scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter $server
    $scripter.Options.ScriptSchema = $true                   # Script schema only
    $scripter.Options.ScriptData = $false                    # No data scripting
    $scripter.Options.ToFileOnly = $true                     # Output to file
    $scripter.Options.IncludeHeaders = $true                 # Include headers
    $scripter.Options.AppendToFile = $false                  # Overwrite file
    $scripter.Options.Encoding = [System.Text.Encoding]::UTF8
    $scripter.Options.ScriptDrops = $false                   # Don't script DROP statements
    $scripter.Options.IncludeDatabaseContext = $true         # Include "USE [DatabaseName]"

    # Iterate through all stored procedures
    Write-Host "Scripting out stored procedures..." -ForegroundColor Green
    foreach ($storedProcedure in $database.StoredProcedures) {
        if ($storedProcedure.IsSystemObject -eq $false) {
            $scriptName = "$($storedProcedure.Schema)_$($storedProcedure.Name).sql"
            $scriptPath = Join-Path -Path $outputFolder -ChildPath $scriptName

            # Configure output file
            $scripter.Options.FileName = $scriptPath

            # Generate the script
            $scripter.Script($storedProcedure)

            Write-Host "Scripted: $scriptName" -ForegroundColor Yellow
        }
    }

    Write-Host "All stored procedures have been scripted to: $outputFolder" -ForegroundColor Green
}
catch {
    Write-Error "Error: $($_.Exception.Message)"
}