Discovering SQL-related cmdlets and modules

In order to be good at working with SQL Server and PowerShell, knowing how to explore and discover cmdlets, snapins, and modules is necessary.

Getting ready

Launch PowerShell ISE as administrator. If you prefer the console, you can also launch that instead, but ensure you are running it as administrator.

How to do it...

In this recipe, we will explore SQL-related cmdlets and modules:

1. To find out how many SQL-related cmdlets are in your system, type the following in your PowerShell editor and run:

   #how many commands from modules that
   #have SQL in the name
   (Get-Command -Module "*SQL*" –CommandType Cmdlet).Count

2. To list the SQL-related cmdlets, type the following in your PowerShell editor and run:

   #list all the SQL-related commands
   Get-Command -Module "*SQL*" –CommandType Cmdlet |
   Select-Object CommandType, Name, ModuleName |
   Sort-Object -Property ModuleName, CommandType, Name |
   Format-Table –AutoSize

3. To see which of these modules are loaded in your PowerShell session, type the following in your editor and run:

   Get-Module -Name "*SQL*"

   If you have already used any of the cmdlets in the previous step, then you should see both SQLPS and SQLASCMDLETS. Otherwise, you will need to load these modules before you can use them.

4. To explicitly load these modules, type the following and run:

   Import-Module -Name "SQLPS"

Note that SQLASCMDLETS will be loaded when you load SQLPS.

How it works...

At the core of PowerShell, we have cmdlets. A cmdlet (pronounced commandlet) is defined in MSDN as lightweight command that is used in the Windows PowerShell environment. It can be a compiled, reusable .NET code or an advanced function, or it can be a workflow that typically performs a very specific task. All cmdlets follow the verb-noun naming notation.

PowerShell ships with many cmdlets. In addition, many applications now also ship with their own cmdlets. For example, SharePoint has a fair number of PowerShell cmdlets that help with installation, configuration, and administration of the farm, sites, and everything in between. A list of cmdlets for SharePoint 2013 can be found at https://technet.microsoft.com/en-us/library/ff678226.aspx.

A legacy way of extending PowerShell is by registering additional snapins. A Snapin is a binary, or a DLL, that can contain a cmdlet. You can create your own snapin by building your own .NET source, compiling, and registering the snapin. You will always need to register snapins before you can use them. Snapins are a popular way of extending PowerShell.

The following table summarizes common tasks with snapins:

Task	Syntax
List loaded Snapins	Get-PSSnapin
List Installed Snapins	Get-PSSnapin -Registered
Show commands in a Snapin	Get-Command -Module "SnapinName"
Load a specific Snapin	Add-PSSnapin "SnapinName"

Since PowerShell v2, modules are introduced as the improved and preferred method of extending PowerShell. A module is a package that can contain cmdlets, providers, functions, variables, and aliases. In PowerShell v2, modules are not loaded by default, so required modules need to be explicitly imported.

Common tasks with modules are summarized in the following table:

Task	Syntax
List loaded Modules	Get-Module
List Installed Modules	Get-Module -ListAvailable
Show commands in a Module	Get-Command -Module "ModuleName"
Load a specific Module	Import-Module -Name "ModuleName"

One of the improved features of PowerShell v3 onwards is support for autoloading modules. You do not need to always explicitly load modules before using the contained cmdlets. Using the cmdlets in your script is enough to trigger PowerShell to load the module that contains it.

SQL Server 2014 modules are located at PowerShell | Modules in the install directory.

There's more...

You can get a list of SQLPS and SQLASCMDLETS by running the following command:

Get-Command -CommandType Cmdlet -Module SQLPS,SQLASCMDLETS|
Select-Object Name, Module |
Sort-Object Module, Name |
Format-Table -AutoSize

Here's the list of cmdlets as of this version of SQL Server 2014:

CommandType Name                                     ModuleName
----------- ----                                     ----------
     Cmdlet Add-RoleMember                           SQLASCMDLETS
     Cmdlet Backup-ASDatabase                        SQLASCMDLETS
     Cmdlet Invoke-ASCmd                             SQLASCMDLETS
     Cmdlet Invoke-ProcessCube                       SQLASCMDLETS
     Cmdlet Invoke-ProcessDimension                  SQLASCMDLETS
     Cmdlet Invoke-ProcessPartition                  SQLASCMDLETS
     Cmdlet Merge-Partition                          SQLASCMDLETS
     Cmdlet New-RestoreFolder                        SQLASCMDLETS
     Cmdlet New-RestoreLocation                      SQLASCMDLETS
     Cmdlet Remove-RoleMember                        SQLASCMDLETS
     Cmdlet Restore-ASDatabase                       SQLASCMDLETS
     Cmdlet Add-SqlAvailabilityDatabase              SQLPS
     Cmdlet Add-SqlAvailabilityGroupListenerStaticIp SQLPS
     Cmdlet Add-SqlFirewallRule                      SQLPS
     Cmdlet Backup-SqlDatabase                       SQLPS
     Cmdlet Convert-UrnToPath                        SQLPS
     Cmdlet Decode-SqlName                           SQLPS
     Cmdlet Disable-SqlAlwaysOn                      SQLPS
     Cmdlet Enable-SqlAlwaysOn                       SQLPS
     Cmdlet Encode-SqlName                           SQLPS
     Cmdlet Get-SqlCredential                        SQLPS
     Cmdlet Get-SqlDatabase                          SQLPS
     Cmdlet Get-SqlInstance                          SQLPS
     Cmdlet Get-SqlSmartAdmin                        SQLPS
     Cmdlet Invoke-PolicyEvaluation                  SQLPS
     Cmdlet Invoke-Sqlcmd                            SQLPS
     Cmdlet Join-SqlAvailabilityGroup                SQLPS
     Cmdlet New-SqlAvailabilityGroup                 SQLPS
     Cmdlet New-SqlAvailabilityGroupListener         SQLPS
     Cmdlet New-SqlAvailabilityReplica               SQLPS
     Cmdlet New-SqlBackupEncryptionOption            SQLPS
     Cmdlet New-SqlCredential                        SQLPS
     Cmdlet New-SqlHADREndpoint                      SQLPS
     Cmdlet Remove-SqlAvailabilityDatabase           SQLPS
     Cmdlet Remove-SqlAvailabilityGroup              SQLPS
     Cmdlet Remove-SqlAvailabilityReplica            SQLPS
     Cmdlet Remove-SqlCredential                     SQLPS
     Cmdlet Remove-SqlFirewallRule                   SQLPS
     Cmdlet Restore-SqlDatabase                      SQLPS
     Cmdlet Resume-SqlAvailabilityDatabase           SQLPS
     Cmdlet Set-SqlAuthenticationMode                SQLPS
     Cmdlet Set-SqlAvailabilityGroup                 SQLPS
     Cmdlet Set-SqlAvailabilityGroupListener         SQLPS
     Cmdlet Set-SqlAvailabilityReplica               SQLPS
     Cmdlet Set-SqlCredential                        SQLPS
     Cmdlet Set-SqlHADREndpoint                      SQLPS
     Cmdlet Set-SqlNetworkConfiguration              SQLPS
     Cmdlet Set-SqlSmartAdmin                        SQLPS
     Cmdlet Start-SqlInstance                        SQLPS
     Cmdlet Stop-SqlInstance                         SQLPS
     Cmdlet Suspend-SqlAvailabilityDatabase          SQLPS
     Cmdlet Switch-SqlAvailabilityGroup              SQLPS
     Cmdlet Test-SqlAvailabilityGroup                SQLPS
     Cmdlet Test-SqlAvailabilityReplica              SQLPS
     Cmdlet Test-SqlDatabaseReplicaState             SQLPS
     Cmdlet Test-SqlSmartAdmin                       SQLPS

To learn more about these cmdlets, use the Get-Help cmdlet. For example, here's the command to learn more about Invoke-Sqlcmd:

Get-Help Invoke-Sqlcmd
Get-Help Invoke-Sqlcmd -Detailed
Get-Help Invoke-Sqlcmd -Examples
Get-Help Invoke-Sqlcmd -Full

You can also check out the MSDN article on SQL Server Database Engine Cmdlets at http://msdn.microsoft.com/en-us/library/cc281847.aspx.

When you load the SQLPS module, several assemblies are loaded into your host.

To get a list of SQLServer-related assemblies loaded with the SQLPS module, use the following script, which will work in both PowerShell v2 and v3:

Import-Module SQLPS –DisableNameChecking

[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object {$_.FullName -match "SqlServer" } |
Select-Object FullName

If you want to run on v3 or newer versions, you can take advantage of the simplified syntax:

Import-Module SQLPS –DisableNameChecking

[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Match "SqlServer" |
Select-Object FullName

This will show you all the loaded assemblies, including their public key tokens: