T-sql – Generate script for all stored procedures in Azure SQL database

azure-sql-databasepowershellstored-procedurest-sql

I'm trying to create a SQL script that will create all the stored procedures that were exported from another database.

Basically navigating in SSMS to the database > Generate Scripts > select stored procedures > ..

I found this query that lists all stored procedures CREATE statements, but the question is how to get all of these rows (result) in a single query/file?

select  mod.definition
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select p.name + ' ' + TYPE_NAME(p.user_type_id) + ', ' 
             from sys.parameters p
             where p.object_id = obj.object_id 
                   and p.parameter_id != 0 
             for xml path ('') ) par (parameters)
where obj.type in ('P', 'X')

I tried via a PowerShell script, but the commands are cut short…

The PowerShell script:

Param
( 
    [Parameter(Mandatory=$false)][string]$SqlServer = "sql_server_name",
    [Parameter(Mandatory=$False)][string]$SqlDatabase = "db_name",
    [Parameter(Mandatory=$false)][string]$destination = "C:\Users\userx\Desktop\",
    [Parameter(Mandatory=$false)][string]$SqlUser = "sqluser",
    [Parameter(Mandatory=$false)][string]$SqlPassword = "password"
)

function Invoke-Sql
{
    $ErrorActionPreference = "Stop"

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$($SqlServer); Database=$($SqlDatabase); User ID=$($SqlUser); Password=$($SqlPassword)"

    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = Get-Content "$PSScriptRoot\Get_Create_Statement_All_Stored_Procedures.sql" | Out-String
    $sqlCmd.Connection = $sqlConnection

    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCmd
    $dataTable = New-Object System.Data.DataTable
    try
    {
        $sqlConnection.Open()
        $sqlOutput = $sqlAdapter.Fill($dataTable)
        Write-Output -Verbose $sqlOutput
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
    catch
    {
        Write-Output -Verbose "Error executing SQL on database [$SqlDatabase] on server [$SqlServer]."
        return $null
    }


    if ($dataTable) { return ,$dataTable } else { return $null }
}

cls

cd $destination

Invoke-Sql | Out-File -FilePath $destination\Create_All_Stored_Procedures.sql

PS. This concerns an Azure SQL database hosted in a Microsoft managed subscription. It seems that using SMO doesn't work on the database as the $smoServer.Databases attribute is empty.

Related Question