Generate create/drop external table scripts on Synapse SQL using PowerShell DbaTools

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Azure Synapse SQL Analytics is Azure Data analytic solution that contains almost 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 can you 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>.

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.

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.