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.
#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\localhost\default\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
Your output should look something like this:
PS SQLSERVER:\sql\WIN7\DATABASES\ADVENTUREWORKS\TABLES> $PTH = get-item Production.TransactionHistory
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.