PowerShell Week at SQL University – Post 3: Providers, PSDrive

Providers

What are Providers? Providers in PowerShell are a hierarchical namespace way flattening out an infrastructure to make it accessible and traverse-able just like a directory structure hard drive. Why do you need them? Well you quite possibly may not ‘need’ them per-say but they sure do make things easier to work with from time to time. In case you don’t already know, you can traverse your SQL Server [2008] as if it were just another drive in either SSMS 2008 or in the PowerShell ISE if you add the Provider Snapin.

To make sure that you have this snapin installed on your machine see yesterday’s post but this time we are going to add the ProviderSnapin instead of the CmdletSnapin.

To do that just run

add-pssnapin SqlServerProviderSnapin100

and poof you can traverse SQL Server. Let’s take a look at what we might see. Run

get-psdrive

and you should see a list of drives including SQL Server. Now if you have more that one instance you will be able to access all of them through this one “SQLServer:\” PSDrive that you now have. In a lot cases what we find inside of these drives will be similar to what we see in the Object Explorer view in SSMS but almost always have something extra too. Let’s take a look at that here by changing directories down to our databases

cd SQLSERVER:\sql\YourComputerName\YourInstanceName\

cd SQLSERVER:\sql\WIN7\KILIMANJARO\

Now let’s go ahead and run the good old “Dir” command and we should get back something like this

dir

As I mentioned before, we got back more than we see if we expand our SQL Server node in the Object Explorer window of SSMS, now if we CD to Databases and do another dir we will see not just more than what we see in SSMS, we see what at first glace to a SQL person is just garbage (but it’s not).

cd Databases
dir

What you’re seeing here is the Methods and Properties of the databases in your instance. What are Methods and Properties? Well if you’re like I was 6 months ago you have no idea what Methods and Properties are. Methods are the Verbs of what you can do to your database (Create, Rename, Drop, Shrink) and Properties and the Adjectives that describe your database (Collation, CreateDate, Compatibility Level, Owner, AutoShrink Enabled). To get just the list of the names of your databases you’re going to have to run this:

dir | select-object name

Ok so now we can see a list of databases that our Provider made available to us. So what? Well I’m going to dive a little deeper tomorrow but for now let’s just go with one final example on scripting out tables (more on that here). Run this: cd AdventureWorks\Tables Then this:

foreach ($tbl in dir )
{
$k="C:\Temp\" + $($tbl.name) + "_table.SQL"
$tbl.Script() > $k
}

I really hope you have temp directory on your C:\ drive. If so go have a look what’s there now 🙂

So is that it? Almost. While SQL Server 2008 only comes with one provider and it’s for the Relational Engine a few people have taken it upon themselves to create their own Provider for things like Analysis Services, Reporting Services, and event BizTalk up on the codeplex site. I can say that I have tried the Analysis Services project and it does work.