Introduction
Powershell is a powerful command shell used to automate tasks. You can automate Windows tasks, like killing Windows processes or services. Also, you can administer SQL Server and Azure SQL Database. Azure Data Studio includes an extension for PowerShell. In this article, we will install the PowerShell extension in Azure Data Studio, work with the terminals, and use some cmdlets commands.
Requirements
- First of all, SQL Server installed
- Secondly, Azure Data Studio installed.
Install the PowerShell extension
First of all, go to extensions and search for PowerShell and then install the extension.
PowerShell extensionSecondly, if everything is OK. You will see the PowerShell icon installed.
PowerShellWorking with PowerShell in Azure Data Studio
If you do not see the Terminal for PowerShell, go to View and select Terminal in the menu. You can have multiple terminals like in Linux. PowerShell sometimes freezes, so having additional terminals can be useful.
PowerShell terminalThe + icon allows adding new terminal sessions for PowerShell.
New terminalYou can also Split the terminals using the Split option from the menu and selecting PowerShell (Default).
Split terminals
Finally, in the UI, you can see the cmdlets or commands used in PowerShell.
PowerShell cmdletsUsing Powershell Commands
First, the cmdlet you often need to use is Get-Help. This command will provide help with commands and examples.
Get-Help
This command provides a short and long description of the command itself, as you can see below.
get helpAlso, if you need examples, you can run using the examples parameter.
Get-Help -examples
The following example shows examples and help for the Get-SqlDatabase cmdlet:
Get-Help Get-SqlDatabase -examples
If you want to go to SQLPS just write sqlps in the command line:
In addition, you can go to SQL using the dir or ls commands. Write ls. Ls or dir are used to list files or directories. In PowerShell, the dir, ls, or get-child items lits the contained items in the current item.
ls
The ls will show you the physical folders and files. If you want to go to SQL server, write this:
cd sqlserver:
If you write ls or dir here you will see all the items of SQL Server like they were folders.
ls
You can use the cd to change the directory and go to different items of the database. You can go to the databases section with these commands:
cd sql\Servername\instancename\database
Where server name is the SQL Server name and the instance name is the name of the SQL Server instance whose name default by default. Finally, we will work with the SQL cmdlets.
Working with SQL Cmdlets
Firstly, in the database node, we will run the Get-SqlDatabase cmdlet. This will list the databases, the names, status, recovery model, compatibility level, owner, and Containment Type.
Get-SqlDatabase
Get databasesAlso, if you want to get the information of a single database, you can use the database name parameter. The following example will show the information of the msdb system database.
Get-SqlDatabase -Name "msdb"
the cmdlet to get database namesIn addition, we have the Invoke-Sqlcmd cmdlet. This cmdlet allows using T-SQL commands in PowerShell. Here is an example:
Invoke-Sqlcmd -Query "sp_who"
execute SQL commandIn the previous example, we executed the sp_who system procedure using PowerShell.
Finally, the next example will run the SELECT GETDATE() command:
Invoke-Sqlcmd -Query "SELECT GETDATE() AS servertime"
Conclusion
To conclude we can say that Azure Data Studio provides a nice extension to run PowerShell. Also, we learned how to install it and use it. How to add terminals and we also applied some commands. PowerShell is a powerful shell that can help us a lot to automate our daily tasks. Finally, if you want to learn more about PowerShell, I strongly recommend reading our articles related.
