PowerShell Editor Support for Azure Data Studio

04/19/2019

6 minutes to read





In this article

This extension provides rich PowerShell editor support in Azure Data Studio. Now you can write and debug PowerShell scripts using the excellent IDE-like interface that Azure Data Studio provides.

Features

Syntax highlighting

Code snippets

IntelliSense for cmdlets and more

Rule-based analysis provided by PowerShell Script Analyzer

Go to Definition of cmdlets and variables

Find References of cmdlets and variables

Document and workspace symbol discovery

Run selected selection of PowerShell code using F8

Launch online help for the symbol under the cursor using Ctrl + F1

+ Basic interactive console support!

Installing the Extension

You can install the official release of the PowerShell extension by following the steps in the Azure Data Studio documentation. In the Extensions pane, search for "PowerShell" extension and install it there. You will get notified automatically about any future extension updates!

You can also install a VSIX package from our Releases page and install it through the command line:

azuredatastudio --install-extension PowerShell-<version>.vsix

Platform support

Windows 7 through 10 with Windows PowerShell v3 and higher, and PowerShell Core

with Windows PowerShell v3 and higher, and PowerShell Core Linux with PowerShell Core (all PowerShell-supported distributions)

with PowerShell Core (all PowerShell-supported distributions) macOS with PowerShell Core

Read the FAQ for answers to common questions.

Installing PowerShell Core

If you are running Azure Data Studio on macOS or Linux, you may also need to install PowerShell Core.

PowerShell Core is an Open Source project on GitHub. For more information on installing PowerShell Core on macOS or Linux platforms, see the following articles:

Example Scripts

There are some example scripts in the extension's examples folder that you can use to discover PowerShell editing and debugging functionality. Check out the included README.md file to learn more about how to use them.

This folder can be found at the following path:

$HOME/.azuredatastudio/extensions/ms-vscode.PowerShell-<version>/examples

or if you're using the preview version of the extension

$HOME/.azuredatastudio/extensions/ms-vscode.powershell-preview-<version>/examples

To open/view the extension's examples in Azure Data Studio, run the following code from your PowerShell command prompt:

azuredatastudio (Get-ChildItem $Home\.azuredatastudio\extensions\ms-vscode.PowerShell-*\examples)[-1]

Creating and opening files

To create and open a new file inside the editor, use the New-EditorFile from within the PowerShell Integrated Terminal.

PS C:\temp> New-EditorFile ExportData.ps1

This command works for any file type, not just PowerShell files.

PS C:\temp> New-EditorFile ImportData.py

To open one or more files in Azure Data Studio, use the Open-EditorFile command.

Open-EditorFile ExportData.ps1, ImportData.py

No focus on console when executing

For those users who are used to working with SSMS, you're used to being able to execute a query, and then being able to re-execute it again without having to switch back to the query pane. In this case, the default behavior of the code editor may feel strange to you. To keep the focus in the editor when you execute with F8 change the following setting:

"powershell.integratedConsole.focusConsoleOnExecute": false

The default is true for accessibility purposes.

Be aware this setting will prevent the focus from changing to the console, even when you use a command that explicitly calls for input, like Get-Credential .

SQL PowerShell Examples

In order to use these examples (below), you need to install the SqlServer module from the PowerShell Gallery.

Install-Module -Name SqlServer

Note With version 21.1.18102 and up, the SqlServer module supports PowerShell Core 6.2 and up, in addition to Windows PowerShell.

In this example, we use the Get-SqlInstance cmdlet to Get the Server SMO objects for ServerA & ServerB. The default output for this command will include the Instance name, version, Service Pack, & CU Update Level of the instances.

Get-SqlInstance -ServerInstance ServerA, ServerB

Here is a sample of what that output will look like:

Instance Name Version ProductLevel UpdateLevel HostPlatform HostDistribution ------------- ------- ------------ ----------- ------------ ---------------- ServerA 13.0.5233 SP2 CU4 Windows Windows Server 2016 Datacenter ServerB 14.0.3045 RTM CU12 Linux Ubuntu

The SqlServer module contains a Provider called SQLRegistration which allows you to programatically access the following types of saved SQL Server connections:

Database Engine Server (Registered Servers)

Central Management Server (CMS)

Analysis Services

Integration Services

Reporting Services

In the following example, we will do a dir (alias for Get-ChildItem ) to get the list of all SQL Server instances listed in your Registered Servers file.

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse

Here is a sample of what that output could look like:

Mode Name ---- ---- - ServerA - ServerB - localhost\SQL2017 - localhost\SQL2016Happy - localhost\SQL2017

For many operations that involve a database, or objects within a database, the Get-SqlDatabase cmdlet can be used. If you supply values for the both the -ServerInstance and -Database parameters, only that one database object will be retrieved. However, if you specify only the -ServerInstance parameter, a full list of all databases on that instance will be returned.

Here is a sample of what that output will look like:

Name Status Size Space Recovery Compat. Owner Available Model Level ---- ------ ---- ---------- -------- ------- ----- AdventureWorks2017 Normal 336.00 MB 57.01 MB Simple 140 sa master Normal 6.00 MB 368.00 KB Simple 140 sa model Normal 16.00 MB 5.53 MB Full 140 sa msdb Normal 48.44 MB 1.70 MB Simple 140 sa PBIRS Normal 144.00 MB 55.95 MB Full 140 sa PBIRSTempDB Normal 16.00 MB 4.20 MB Simple 140 sa SSISDB Normal 325.06 MB 26.21 MB Full 140 sa tempdb Normal 72.00 MB 61.25 MB Simple 140 sa WideWorldImporters Normal 3.2 GB 2.6 GB Simple 130 sa

This next example uses the Get-SqlDatabase cmdlet to retrieve a list of all databases on the ServerB instance, then presents a grid/table (using the Out-GridView cmdlet) to select which databases should be backed up. Once the user clicks on the "OK" button, only the highlighted databases will be backed up.

Get-SqlDatabase -ServerInstance ServerB | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption On

This example, again, gets list of all SQL Server instances listed in your Registered Servers file, then calls the Get-SqlAgentJobHistory which reports every failed SQL Agent Job since Midnight, for each SQL Server instance listed.

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse | WHERE {$_.Mode -ne 'd' } | FOREACH { Get-SqlAgentJobHistory -ServerInstance $_.Name -Since Midnight -OutcomesType Failed }

In this example, we will do a dir (alias for Get-ChildItem ) to get the list of all SQL Server instances listed in your Registered Servers file, and then use the Get-SqlDatabase cmdlet to get a list of Databases for each of those instances.

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse | WHERE { $_.Mode -ne 'd' } | FOREACH { Get-SqlDatabase -ServerInstance $_.Name }

Here is a sample of what that output will look like:

Name Status Size Space Recovery Compat. Owner Available Model Level ---- ------ ---- ---------- -------- ------- ----- AdventureWorks2017 Normal 336.00 MB 57.01 MB Simple 140 sa master Normal 6.00 MB 368.00 KB Simple 140 sa model Normal 16.00 MB 5.53 MB Full 140 sa msdb Normal 48.44 MB 1.70 MB Simple 140 sa PBIRS Normal 144.00 MB 55.95 MB Full 140 sa PBIRSTempDB Normal 16.00 MB 4.20 MB Simple 140 sa SSISDB Normal 325.06 MB 26.21 MB Full 140 sa tempdb Normal 72.00 MB 61.25 MB Simple 140 sa WideWorldImporters Normal 3.2 GB 2.6 GB Simple 130 sa

Reporting Problems

If you experience any problems with the PowerShell Extension, see the troubleshooting docs for information on diagnosing and reporting issues.

Security Note

For any security issues, see here.

Contributing to the Code

Check out the development documentation for more details on how to contribute to this extension!

Maintainers

License

This extension is licensed under the MIT License. For details on the third-party binaries that we include with releases of this project, see the third-party notices file.

Code of Conduct

This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.