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.

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.