4 Essential PowerShell Provider Commands for SQL Server

Windows PowerShell commands can be a valuable addition to your SQL Server management tools. Although I don’t think PowerShell is going to replace SQL Server Management Studio (SSMS) anytime soon, it can be used for a wide range of scripted management tasks. PowerShell can run T-SQL commands and also work with objects outside of the SQL Server database. You can use the SQL Server PowerShell Provider to navigate and manage SQL Server database objects, and PowerShell scripts can be run by SQL Agent. In addition, you can import and execute SMO or ADO.NET assemblies by using PowerShell. In this column, I’ll cover four of the essential PowerShell Provider commands that can help you get started using PowerShell with SQL Server.

#1: Import the sqlps Module

If you use the Start PowerShell option from the SSMS context menu, then the sqlps module is automatically loaded for you. However, if you want to use the SQL Server PowerShell provider from a regular PowerShell prompt, you’ll first need to import the sqlps module into PowerShell. You can import it by typing the following:

Import-Module sqlps

A warning message appears because the SQL Server team used two unapproved cmdlet verbs. The unapproved cmdlet verbs are Encode and Decode. If you use the DisableNameChecking switch below, you won’t see the warning:

Import-Module sqlps –DisableNameChecking

You can see the loaded modules by using the following command:

Get-Module

You can see all of the available modules by adding the ListAavailable parameter to the Get-Module command:

Get-Module –ListAvailable

#2: Navigating with the SQL Server Power Provider

The SQL Server PowerShell provider enables a simple navigation mechanism similar to navigating the file system with Windows Shell commands. You can use familiar commands such as cd and dir to navigate the paths, similar to the way you navigate folders in a command prompt window. (Note that cd and dir are actually PowerShell aliases for Set-Location and Get-ChildItem, respectively.)

You can also use other commands such as ren (Rename-Item) or del (Remove-Item) to perform actions on the different nodes in the path. You can see how to list the databases in a server called orportvm1 using the PowerShell code below. Remember, you would need to substitute that name of your own server for orportvm1:

PS SQLSERVER:> cd sqlorportvm1defaultdatabasesPS SQLSERVER:sqlorportvm1defaultdatabases> dir

Related: Why PowerShell for SQL Server?

To list the objects in the Adventurework2012 database you would use the following code.

PS SQLSERVER:sqlorportvm1defaultdatabases> cd AdventureWorks2012PS SQLSERVER:sqlorportvm1defaultdatabasesAdventureWorks2012> dir

To list all of the tables in the AdvantureWorks2012 database you would execute the following.

PS SQLSERVER:sqlorportvm1defaultdatabasesAdventureWorks2012> cd tablesPS SQLSERVER:sqlorportvm1defaultdatabasesAdventureWorks2012tables> dir

#3: Running T-SQL Commands with Invoke-SqlCmd

In addition to being able to navigate through database objects, the SQL Server PowerShell Provider Invoke-SqlCmd cmdlets can also execute T-SQL commands. You can see how the SQL Server Provider can run T-SQL commands below:

PS SQLSERVER:sql> Invoke-Sqlcmd -Query "SELECT @@VERSION;"

You can also execute data queries using the Invoke-Sqlcmd cmdlet as you can see in the following example:

PS SQLSERVER:sql> Invoke-Sqlcmd -Query "SELECT * FROM HumanResources.Department;" –Database “AdventureWorks2012”

You can pass PowerShell variables into your queries and return the results in variables as well. The following example shows how to use PowerShell variables with queries, creating a new variable name $HRName and setting it to the value of Sales. That variable is then used in the –Query parameter of the Invoke-Sqlcmd cmdlets. The results are placed in the variable name $HRGroupName.

PS SQLSERVER:> $HRName = “Sales”PS SQLSERVER:> $HRGroupName = Invoke-Sqlcmd -Query "SELECT GroupName from HumanResources.Department where Name = '$($HRName)'" –Database “AdventureWorks2012”

You can see the contents of the results variable by using the write-host cmdlet:

PS SQLSERVER:> write-host $HRGroupName.GroupName

#4: Getting Help

One of the hurdles to using PowerShell is knowing what commands and parameters to use. Learning how to make effective use of PowerShell’s built-in Help commands is a must for learning how to use the different cmdlets. The built-in Help can provide instructions as well as examples. To get help with the provider, use this command:

PS SQLSERVER:sql> Get-Help SQLServer

You can also use the Get-Help cmdlets to dig deeper into the various cmdlets themselves and get examples for how to use them:

PS SQLSERVER:sql> Get-Help Invoke-Sqlcmd -Examples