SQLServer:\SQL\Databases\Tables> Dir

Update 2016-07-01:

If you are using SSMS 2012 or SSMS 2014 you will need to run Import-Module SQLPS to load the SQL Server PowerShell Provider.
If you are using SSMS 2016 specifically SSMS 16.3+ (download latest version here sqlps.io/dl ) you can run Import-Module SqlServer for a much improved SQL Server PowerShell Provider experience.

Getting started talking to your SQL Servers in the PowerShell 2.0 ISE

By now hopefully you’ve noticed that SQL Server 2008 comes with PowerShell all over the place.  Just about anything you click on in Object Explorer has “Start PowerShell” and you can even execute PowerShell steps in SQL Agent Jobs.

You may be wondering why on Earth you would want to “Start PowerShell” in the first place.  I’ll show you one thing today and then expand on that in later blog posts but today I wanted to tell you how to run PowerShell in something a little more organized than a command prompt window.  The first thing I’d like you to do is open up SSMS, connect to an instance and Right-Click the Databases folder > then select Start PowerShell.  A command prompt window should open up and from there you can talk to your SQL Server instance as if was a drive on your machine:


The SQLPS window that just opened up is PowerShell 1.0 with a special ‘expansion pack’ that allows it to talk to SQL Server in a way that PowerShell alone can’t.  So now that we’ve got that covered I want to show you how to do the same thing in PowerShell 2.0’s ISE.

Go to Start > All Programs > Accessories > Windows PowerShell > Windows PowerShell ISE

Copy the code from the bottom and step through it like I did in this picture.  When you’re done you will be able to drill down to tables like they were just folders directories on your hard drive.  If you aren’t running Windows 7 (upgrade already!) check this post and see if you’re operating system is eligible.


When you get to the part where you do “get-psdrive” for the second time you should now see the SQL Server on your local machine.  If you haven’t figured out yet, this is only going to work if you have SQL Server installed on your local machine.

#Snapins that are running            
#Snapins that you can load            
get-pssnapin -registered             
#Go ahead and add the SQL Snapins            
add-pssnapin SqlServerCmdletSnapin100            
add-pssnapin SqlServerProviderSnapin100             
#New Resource            
#What can we do with that?            

Now that we have done all of that we can do something that I have seen mentioned several places but I think that Allen White’s blog post explains it the best (here’s my version of it).  You can now create a variable and populate it with one the DDL structures in your database and from there generate a Create script for it like so:

$PTH = get-item Production.TransactionHistory

Your output should look something like this:
PS SQLSERVER:\sql\WIN7\DATABASES\ADVENTUREWORKS\TABLES> $PTH = get-item Production.TransactionHistory
CREATE TABLE [Production].[TransactionHistory](
[TransactionID] [int] IDENTITY(100000,1) NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL

And that’s it for this edition of how to do something when you open up the PowerShell 2.0 ISE.  Next we’ll do something more useful I promise.

Please Share This:

You may also like:

One Response

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner