The key distinguishing feature of the SqlServer module is the SQL Provider, enabling users to mount a PSDrive and navigate SQL instances as if they were files and folders. This is very useful when discovering an unknown SQL instance's layout, as listing its structure is literally a simple act of running the following:

Get-ChildItem -Recurse

Of special significance about this provider is that if your current path is in one particular database, several commands will use the current path's properties as parameters specifying the target, taking precedence over explicitly bound parameters, unless disabled. This is not consistently implemented across the module; all commands that do this contain a -IgnoreProviderContext parameter.

After importing the module, you can navigate to the root path of SQL Server path by executing the following:

SQLSERVER:

With this, you are in the root folder of the SQL drive. Running Get-ChildItem will now display the various kinds of services SQL can connect to, as follows:

Name            Root                           Description
----            ----                           -----------
DAC             SQLSERVER:DAC                 SQL Server Data-Tier Application Component
DataCollection  SQLSERVER:DataCollection      SQL Server Data Collection
SQLPolicy       SQLSERVER:SQLPolicy           SQL Server Policy Management
Utility         SQLSERVER:Utility             SQL Server Utility
SQLRegistration SQLSERVER:SQLRegistration     SQL Server Registrations
SQL             SQLSERVER:SQL                 SQL Server Database Engine
SSIS            SQLSERVER:SSIS                SQL Server Integration Services
XEvent          SQLSERVER:XEvent              SQL Server Extended Events
DatabaseXEvent  SQLSERVER:DatabaseXEvent      SQL Server Extended Events
SQLAS           SQLSERVER:SQLAS               SQL Server Analysis Services

When connecting to a regular database instance, the root path to that instance's contents is as follows:

SQLSERVER:SQL<computername><instancename>

For example, connecting to the default instance on computer SQL2017 would be accomplished as follows:

Set-Location SQLSERVER:SQLSQL2017Default

Describing all the details provided by this provider would be equal to an in-depth course on how a SQL Server is designed and operates, and would be far beyond the scope of this book. If this is your core activity, then by all means, feel free to explore!

Just to show one quick example, you can retrieve the information on columns from a table and show a few select properties as follows:

Get-ChildItem SQLSERVER:SQLSQL2017DefaultDatabasesmasterTablesdbo.allcountriescolumns | Format-Table Name, DataType, Collation, Computed, State