Azure Synapse SQL Analytics is Azure Data analytic solution that contains all components and services that you would need to implement data analytic solutions. One important part of Azure Synapse is Synapse SQL serverless query service that enables you to query Azure storage files using pure T-SQL language and external table.
Synapse SQL supports rich T-SQL language that enables most of the tools (even open-source non-Microsoft tools like DbaTools) to work with this new service. In this article you will see how you can use PowerShell DbaTools to script Synapse SQL objects the same way you are doing this in standard SQL Server and Azure SQL Database.
DbaTools is well known community PowerShell tool that simplifies database administration tasks. It contains many useful scripts that can help you automate administration/devops tasks, configure database, and script objects in the database.
One interesting PowerShell script that might be handy is Export-DbaScript that enables you to generate scripts of some selected objects from your database. In Synapse SQL you can list set of external tables from your database and use Export-DbaScript to generate CREATE or DROP scripts. This might be useful if you need to move external tables to another database, or keep scripts under source control.
Installing DbaTools
DBAtools is an open-source PowerShell library that must be installed on the computer that you will use to connect to your Synapse SQL query service. You can review requirements on dbatools download/ page, but if you are already using PowerShell, you probably need to run to following command to install this library:
Install-Module dbatools
Once you install you are ready to try the samples in this article.
Generating 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 on-demand 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 <sql endpoint name> (for example mysynapsesqlendpoint-ondemand), login name that you will use to connect to your Synapse SQL database instead of <login>, and name of the database where your external tables are placed instead of <database name>.
| NOTE: if you have problem connecting to .sql.azuresynapse.net domain, try to switch to .database.windows.net. |
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.
Generating a script that drops all external tables
If you already have external tables in your target database you might want to generate drop script that will drop. Export-DbaScript also simplifies this task and you can use the following script:
$loginName = "<login>"
$synapseSqlName = "<sql endpoint name>"
$databaseName = "<database name>"
$login = Get-Credential -Message "Enter your SQL on-demand password" -UserName $loginName
$options = New-DbaScriptingOption
$options.ScriptDrops = $true
Get-DbaDbTable -SqlInstance "$synapseSqlName.sql.azuresynapse.net" -Database $databaseName -SqlCredential $login | Export-DbaScript -ScriptingOptionsObject $options -Passthru
Again, you need to enter name of Synapse SQL serverless endpoint instead of <sql endpoint name> (for example mysynapsesqlendpoint-ondemand), login name that you will use to connect to your SynapseSQL database instead of <login>, and name of the database where your external tables are placed instead of <database name>.
Note the DbaScriptingOption parameter where you can instruct Export-DbaScript to generate only DROP statements. Using these options you can easily customize the generated scripts.
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.
