0

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.

0

The solution can be found below:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.