SqlDev PowerShell 101: Getting Started with PowerShell

This is the first in a new series focused on PowerShell for SQL developers. That means using PowerShell to create and/or modify table schema, stored procedures, and indexes. In other words, automating code development and database tuning processes. This is mostly what I’ve been using PowerShell for so far, and I have to say, I am LOVING it! It’s allowing me to save *days* of coding and validating.

Disclaimer: I am NOT a PowerShell expert, just a newly converted enthusiast who wants to save you from the headaches I myself have suffered while trying to learn PoSh. So I’ll mostly focus on the “how to’s” instead of the “why’s” in this series. :)

I’m going to start at the very beginning and assume you have never opened PowerShell before in your life. Basically, I’m going to assume you’re where I was just a few short months ago. So let us start by finding the application. ;)

If you’re using Windows 7 or later, the good news is that PowerShell comes pre-installed on the OS. We just need to configure it to allow the execution of scripts. Click on the Start icon –> “Search programs and files” –> type “PowerShell.”

Searching for PowerShell

Searching for PowerShell

The first time we open PowerShell, we will need to run it as Administrator for elevated permissions. To do this, right-click on the Windows PowerShell icon that should now be on your Taskbar, then click on “Run as Administrator.” While you’re there, also add it to your Taskbar and/or Start Menu for easy retrieval in the future.

Running PowerShell as Administrator

Running PowerShell as Administrator

Now we need to change the Execution Policy from “Restricted” — which prevents the execution of PowerShell scripts — to something a little more accessible. For the purposes of this post, we’re going to set it to “RemoteSigned,” with the understanding that this may pose some security risks. You can read more about Execution Policy settings here: Using the Get-ExecutionPolicy Cmdlet.

Similarly, you can run this command inside of PowerShell to get more details:

Get-Help About_Signing

Now that we have PowerShell open in Administrator mode, execute this command:

Set-ExecutionPolicy RemoteSigned

You will be prompted to confirm the change. Type “Y”. You can then confirm that the change has taken place by typing:

Get-ExecutionPolicy
Changing Execution Policy

Changing Execution Policy

Alrighty, now we’re ready to write some PowerShell!

Go ahead and close your PowerShell window, then re-open as you normally would (not in Administrator mode; we don’t need elevated permissions any longer).

Let’s start with some simple commands. For this post, I have scripted all objects from AdventureWorks to a new directory called C:\MyCode\. We can take a look at the files in the directory using a cmdshell command that’s probably very familiar: Dir. Go ahead and run that command now.

Dir
Running Dir in PowerShell

Running Dir in PowerShell

So does that mean PowerShell supports all the old cmdshell commands that we know and love (to hate)? Well, yes and no. The PowerShell devs, forward-thinking folks that they are, knew that having support for cmdshell syntax would probably ease the transition for many people, so they created aliases. You can see this by running the Get-Alias cmdlet:

Get-Alias Dir
Get-Alias Dir

Get-Alias Dir

What we see is that, under the covers, Dir is nothing more than an alias for Get-ChildItem. So let’s switch to using Get-ChildItem from here on out.

One thing that took me a while to discover, but which is very helpful, is the auto-complete functionality inherent in PowerShell. To use this, just start typing a command, such as “Get-Ch”, and hit the Tab key. As you press Tab, PowerShell will iterate through the list of available cmdlets / switches that are available in the current context. Keep hitting Tab until you get to Get-ChildItem, then execute that command.

Get-ChildItem
Get-ChildItem

Get-ChildItem

Hmm, those results look familiar. That’s a good sign. But what else can we do with Get-ChildItem? Let’s find out by using the Get-Help cmdlet:

Get-Help Get-ChildItem
Get-Help Get-ChildItem

Get-Help Get-ChildItem

Type “Get-ChildItem -” (make sure to include the hyphen, or you’ll be tabbing through way more than you’d like) and press Tab to iterate through the list of available switches. Stop when you get to the -Recurse switch. If you go too far, don’t worry… you can hit the Shift+Tab to go in reverse direction. This allows Get-ChildItem to execute against child folders too, so we’ll add that to our command.

Get-ChildItem -Recurse

So far, so good. Now what else can we do?

At this point, we need to discuss piping. Piping is a way to stack multiple cmdlets together to produce a single, final, perfect result. It’s one of the features that adds the “power” in PowerShell. You can read more about piping here: Piping and the Pipeline in Windows PowerShell.

So let’s build our first pipe. We know how to return all the files in a directory, but how do we *search* the directory for files that contain a specific keyword? It turns out it’s actually not that hard… we just need to use the Select-String cmdlet. To do this, press the Up arrow on your keyboard to retrieve the previously executed command, then type a pipe (|) before adding the Select-String cmdlet:

Get-ChildItem -Recurse | Select-String -Pattern "SalesOrderDetail"
Search Files

Search Files

So what’s this doing? Well, it’s iterating through each of the files listed in the directory and subdirectories, then calling the Select-String cmdlet to find files that contain the word “SalesOrderDetail.” It’s even showing us what line the pattern is found on. Pretty awesome, huh? But I think we can do better.

One of the best things about PowerShell is the sheer amount of documentation available. Case in point: TechNet has a quick reference of PowerShell 2.0 cmdlets. Looking at the list, some interesting cmdlets catch my eye, including Select-Object and Get-Unique. If we pipe these cmdlets to our existing command, we get something like this:

Get-ChildItem -Recurse | Select-String -Pattern "SalesOrderDetail" | Sort-Object | Select-Object Path | Get-Unique -AsString
Final PowerShell Command

Final PowerShell Command

This returns a very easy-to-consume list of files that contain the pattern “SalesOrderDetail.” Imagine using this to search through hundreds of files and thousands of lines of code to find dependencies. Powerful PowerShell indeed.

So that’s it for this first post and introduction to PowerShell. If you’re looking for more resources, here are some PowerShell-related sites I’ve found especially helpful:

Happy Scripting! :)

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , . Bookmark the permalink.

13 Responses to SqlDev PowerShell 101: Getting Started with PowerShell

  1. Pingback: SqlDev PowerShell 101: Getting Started with PowerShell - SQL Server - SQL Server - Toad World

  2. Andre Ranieri says:

    Thanks for posting this Michelle!

  3. I’m definitely looking forward to this series.

  4. Nic says:

    Please don’t set your execution policy to be unrestricted. You are opening up a huge security hole in doing so. I would recommend using RemoteSigned instead. It is a great deal more secure and will allow you to execute scripts that you create without any issues.

  5. You’re very welcome, Andre! :)

    Thanks, Edwin! I’m glad you left a comment, as you were another person whose PowerShell posts have helped me a lot. Do you have a single URL that links to all of your PowerShell articles? :)

    Good suggestion, Nic! I’ve updated the post. Thank you for taking the time to leave a comment. :)

  6. All of my PowerShell articles related to SQL Server are on MSSQLTips.com.
    http://www.mssqltips.com/sqlserverauthor/18/edwin-sarmiento/
    I do have several on my blog about stuff that’s unrelated to SQL Server – Hyper-V, AD, SharePoint, Windows, etc.

  7. Thanks, Edwin! I’ve updated the post. :)

  8. Tin Le says:

    I really enjoy this first series of PowerShell because I never done something like this before. It definitely guided me the right way of start to learn something new for me, but old for other.
    Thanks again.

  9. Dexter says:

    Nice article, Michelle.
    If you’re a DBA and plan to work with PowerShell (and you should be), check out Chad Miller’s nifty tool called SQLPSX on CodePlex: http://sqlpsx.codeplex.com/

  10. Pingback: Something for the Weekend - SQL Server Links 17/05/13 • John Sansom

  11. This timing of this post/series is perfect for me, as I recently decided I needed to stop avoiding it and learn PS :) Looking forward to more in the series. In the meantime, I will be reading up on the links you provided.

  12. Pingback: SqlDev PowerShell 102: Working with Data and Variables - SQL Server - SQL Server - Toad World

  13. SQLGlenn says:

    Great job, have gotten further than ever before when trying to use Powershell. Looking forward to more.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>