SQL Server 2008

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.

Cmdlets, Functions and Snapins

So far we’ve learned how to open up PowerShell and add on to it with some modules; then we learned how to create a variable and populate it with anything from some numbers to an entire table. Today we’re going to quickly look at another way of expanding PowerShell with something called a snapin so that we can get to some cmdlets.

First, what are cmdlets? Cmdlets are similar to DOS commands but have some noteworthy differences. In SQL Server terms you might think of cmdlets as the System Stored Procedures of PowerShell and think of functions as the regular user defined Stored Procedures of PowerShell.

Cmdlets and functions accept parameters just like stored procedures do. Just like stored procedures you can even define default values for these parameters in case the user doesn’t have one to pass in. Unlike stored procedures though there is actually a drive that you can go to and see all of your functions. Just run this and you’ll see what I mean dir function:

Snapins are akin to finished product code. They were created for PowerShell 1.0 and were succeeded in PowerShell 2.0 by modules– which are more like the manifest list for deploying a bunch of SSIS packages. If you have SSMS 2008 installed you’ve got yourself two extra snapins already. And if you have Idera’s SQL Diagnostic Manager installed you may be in for a real surprise when you retrieve your list of snapins. Run this command to see all of the extra snapins that are available to you:

get-pssnapin –registered

At the very least you should see this:

If you don’t see those two you’ll need to install SSMS 2008. If you don’t have a copy of that handy you can get the free version of it here. The SqlServerCmdletSnapin100 snapin give us the 5 cmdlets that come with SQL Server 2008 and today we’re going to focus on Invoke-SQLcmd which basically just calls out to good old sqlcmd. To load this just run:

add-pssnapin SqlServerCmdletSnapin100

From there we can do a quick query like this:

invoke-sqlcmd -query “sp_databases” -database master -serverinstance YourServerName\YourInstanceName | format-table

invoke-sqlcmd -query "sp_databases" -database master -serverinstance WIN7\Kilimanjaro | format-table

Now the reason that I brought up functions is because they override cmdlets and that’s really important to know. If you run this command get-command invoke-sqlcmd you will only see one thing returned for invoke-sqlcmd. However if we create a new function like:

function invoke-sqlcmd { get-date }

You end up overriding the Invoke-SQLcmd with your new function. Give it a try, run just this invoke-sqlcmd and you should end up seeing something like this:

So how do you know when you’ve done something like that? Just run get-command invoke-sqlcmd again and this time you will see this:

I realize that we kind of rushed through that info but thankfully there is only one more piece to the basics before we can get on to code that you can use on a daily basis