SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

SQLServer:\SQL\Databases\Tables> Dir

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:

LaunchSQLPS

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.

ISE_SQL

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. 

#Before
get-psdrive

#Snapins that are running
get-pssnapin
#Snapins that you can load
get-pssnapin -registered

#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

#New Resource
get-psdrive

#What can we do with that?
cd SQLSERVER:\SQL\YourComputerName\YourInstanceName\DATABASES\ADVENTUREWORKS\TABLES

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
$PTH.Script()

Your output should look something like this:
PS SQLSERVER:\sql\WIN7\DATABASES\ADVENTUREWORKS\TABLES> $PTH = get-item Production.TransactionHistory
$PTH.Script()
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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
) ON [PRIMARY]

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.

One Comment

  1. Social comments and analytics for this post…

    This post was mentioned on Twitter by SQLvariant: [blog] “SQLServer:\SQL\Databases\Tables>Dir” Getting started w/ SQL Servers in the PowerShell 2.0 ISE http://bit.ly/9DjsLn #SQLPASS…