Generate script for all stored procedures in Azure SQL database

Question

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.

Answer 1

The solution can be found below:

• https://www.geekshangout.com/powershell-an-example-of-how-to-save-a-queries-results-to-a-csv-file/

  # SQL Connection Variables - Change
  $SQLServer = "SQLServerName.mydomain.local" 
  $SQLDBName = "Database_Name" 
  $SQLUsername = "SQLReaderUser"
  $SQLPassword = "MySQLPassword"
  # Where to save the output CSV file - Change
  $OuputFile = "c:\scripts\SQL_Export.csv"
  # Your SQL Query - Change
  $SqlQuery = "SELECT 
   rtrim(EmployeeNumber) as EmployeeNumber,
   rtrim(JobTitle) as JobTitle,
   rtrim(Department) as Department,
   rtrim(Company) as Company,
   rtrim(Location) as Location,
   rtrim(CostCentre) as CostCentre,
   rtrim(ManagerEmployeeNumber) as ManagerEmployeeNumber
   FROM [$SQLDBName].[dbo].[Employee_Basic]"
  # Delete the output file if it already exists
  If (Test-Path $OuputFile ){
   Remove-Item $OuputFile 
  }
  Write-Host "INFO: Exporting data from $SQLDBName to $OuputFile" -foregroundcolor white -backgroundcolor blue
  # Connect to SQL Server using non-SMO class 'System.Data': 
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUsername; Password = $SQLPassword" 
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
  $SqlCmd.CommandText = $SqlQuery 
  $SqlCmd.Connection = $SqlConnection 
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
  $SqlAdapter.SelectCommand = $SqlCmd 
  $DataSet = New-Object System.Data.DataSet 
  $SqlAdapter.Fill($DataSet) 
  $SqlConnection.Close() 
  #Output RESULTS to CSV
  $DataSet.Tables[0] | select "EmployeeNumber","JobTitle","Department","Company","Location","CostCentre","ManagerEmployeeNumber" | Export-Csv $OuputFile
  

• https://www.reddit.com/r/PowerShell/comments/530jhx/csv_file_to_txt_file/

  Export-Csv -Delimiter `t
  

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