Tasks - Generate Scripts in Azure Synapse Serverless SQL

3
votes

I am working with an Azure Synapse Serverless SQL instance, connected via the most recent version of SSMS. I have several external tables defined over parquet files in an Azure Data Lake (Gen2).

My question is how do I use the typical "Tasks-Generate Scripts" to generate the external table definitions similar to what I am used to in SQL Server? Or do something similar (batch export of external table definitions)...at this point, it appears that I need to go external table by external table (right-click -> script table as -> drop and create to -> File, etc.).

Any advice is appreciated, thanks!

1
azureserverlessexternal-tablesazure-synapse

1 Answers

0
votes

Currently this is not supported in the SSMS. You can script external tables one by one as you wrote from the SSMS UI.

We do plan to provide support for this in the future.

You can use PowerShell to script multiple external tables. Have a look at this blog post that we wrote for details.

Summary of the blog post:

  1. Open PowerShell

  2. Run: Install-Module dbatools

  3. Generate a script that creates external tables

    If you have created a set of external tables in Synapse SQL and you want to move their definitions to another instance or keep creation script locally or in some source- control system, you can easily create script that creates all external tables.

If you have installed the latest version of DbaTools you can use the following script to generate CREATE EXTERNAL TABLE scripts for a set of your tables:

    $loginName = "<login>"
    $synapseSqlName = "<sql endpoint name>"
    $databaseName = "<database name>"
    $login = Get-Credential -Message "Enter your SQL serverless password" -UserName $loginName
    $script = Get-DbaDbTable -SqlInstance "$synapseSqlName.sql.azuresynapse.net" -Database $databaseName -SqlCredential $login | Export-DbaScript -Passthru | Out-String
    $script -replace ' NULL', ''

You need to enter name of Synapse SQL serverless endpoint instead of (for example mysynapsesqlendpoint-ondemand), login name that you will use to connect to your Synapse SQL database instead of , and name of the database where your external tables are placed instead of .

Once you run this script, you will see the script that creates external tables in output window.

Export-DbaScript enables you to customize the script and export CREATE TABLE statements in some file, define encoding, omit schema, etc. You have many options that you can use to customize script generation.

In the example above I have used Get-DbaDbTable command to fetch all external tables from my database. You can also customize this command to get the tables from multiple databases or specify set of tables that you want to export.

Conclusion

DbaTools is very useful set of PowerShell commands that can enable you to automate administration tasks in your Synapse SQL databases. In this example you have seen hot to generate scripts that create and drop tables, but you can easily modify this script to create/drop users, databases, etc.