Teach Something New: PowerShell Providers [T-SQL Tuesday #065]

Mike Donnelly (b|t) invited the SQL New Blogger Challenge participants to join in this month’s T-SQL Tuesday, and I’m happy to accept – I’ve been meaning to write for a T-SQL Tuesday for quite some time. The topic is “Teach Something New”, which also happens to work well with a challenge issued to me by Mike Fal (b|t) – to write something about PowerShell.

I’m very new to PowerShell. I’ve wanted to learn it for a long time, but I’ve never taken the time. This spring, I’m making a concerted effort to find ways to use it in my work so I can finally get over that learning curve.

One feature of PowerShell that fascinates me is the provider model. A provider abstracts a hierarchy down to something resembling a path in the file system. In fact, the file system itself is implemented as a hierarchy in PowerShell. PowerShell extends this model, though, by allowing other systems to expose their hierarchies as providers, also.

Let’s start by getting a list of the available providers:

PS C:> Get-PSProvider

On my test VM, this returns a list of the default providers in a tabular format:

Now, let’s get a listing of the files on my C: drive. The PowerShell command for retrieving the list of objects in a provider is Get-ChildItem, but Microsoft aliased that to dir to help ease the transition for those of us most familiar with DOS and the Windows Command Prompt.

So far, so good. It looks very similar to the output that I would have expected from dir at the command prompt – a little more Unix-like, but familiar.

Here’s where it starts to get really interesting. Remember the old env command from DOS that allowed you to list the environment variables? PowerShell implements that as a provider:

The Windows Registry is also exposed via a provider, making it very easy to navigate through the hierarchy:

If I want to dive into the SQL Server registry hive (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server), I can specify that as a path, just as if it was on the file system:

PS C:> cd "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server"

In REGEDIT, finding that portion of the Registry would take quite a bit of looking and a number of clicks. In PowerShell, it becomes trivial.

Microsoft also exposes SQL Server as a provider. Start by importing the SQLPS module:

PS C:> Import-Module SQLPS

On newer versions of PowerShell about unapproved verbs, you’ll get a warning, but that’s fine. Now, run Get-PSProvider again:

You’ll see another provider at the bottom of the list: SqlServer. Switch to that provider as if it was a drive and retrieve a listing:

PS C:> cd SQLSERVER:
PS SQLSERVER:> dir

You’ll see a top-level listing of the object classes within SMO, the SQL Server Management Object model. For now, change to the SQL directory:

PS SQLSERVER:> cd SQL

This brings up a list of the machines known by this provider. Within that are the available instances, and within that are listings of the objects for an individual instance:

From here, it approximates what you would see in the tree view in SQL Server Management Studio. The path to a table would look like this:

SQLSERVER:\SQL\PHOENIX-SQL3\DEFAULT\Databases\EncryptionDemo\Tables\dbo.Users

That should look familiar – it’s the same syntax as you would use to reference a file. Files, registry keys, and tables are just objects to PowerShell, so once you have the path, you can interact with them in more or less the same way. One other benefit: PowerShell’s string substitution works with providers, also, making it a powerful and flexible method of traversing hierarchies.

If you’re new to PowerShell, or if you just want a condensed method of accessing objects in SQL Server, the Registry, and other hierarchies, give providers a try.

---

This is my post for Week 2 of the SQL New Blogger Challenge. Check out all of this week’s posts on Twitter (#SQLNewBlogger).