PowerShell Editor Support for Azure Data Studio
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
- Linux 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/microsoft.powershell-<version>/examples
or if you're using the preview version of the extension
$HOME/.azuredatastudio/extensions/microsoft.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\microsoft.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
- Keith Hill - @r_keith_hill
- Tyler Leonhardt - @TylerLeonhardt
- Rob Holt
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.