Generate script for all stored procedures in Azure SQL database 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 &gt; Generate Scripts &gt; select stored procedures &gt; ..
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 = &quot;sql_server_name&quot;,
    [Parameter(Mandatory=$False)][string]$SqlDatabase = &quot;db_name&quot;,
    [Parameter(Mandatory=$false)][string]$destination = &quot;C:\Users\userx\Desktop\&quot;,
    [Parameter(Mandatory=$false)][string]$SqlUser = &quot;sqluser&quot;,
    [Parameter(Mandatory=$false)][string]$SqlPassword = &quot;password&quot;
)

function Invoke-Sql
{
    $ErrorActionPreference = &quot;Stop&quot;

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

    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = Get-Content &quot;$PSScriptRoot\Get_Create_Statement_All_Stored_Procedures.sql&quot; | 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 &quot;Error executing SQL on database [$SqlDatabase] on server [$SqlServer].&quot;
        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.
The solution can be found below:

https://www.geekshangout.com/powershell-an-example-of-how-to-save-a-queries-results-to-a-csv-file/
https://www.reddit.com/r/PowerShell/comments/530jhx/csv_file_to_txt_file/

First save as .csv and later convert to .txt or .sql