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.”
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.
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:
Now that we have PowerShell open in Administrator mode, execute this command:
You will be prompted to confirm the change. Type “Y”. You can then confirm that the change has taken place by typing:
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.
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:
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.
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:
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.
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"
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
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:
- Overview of Cmdlets Available in Windows PowerShell
- A Task-Based Guide to Windows PowerShell Cmdlets
- Hey, Scripting Guy! Blog
- Allen White’s PowerShell posts
- Kendal Van Dyke’s PowerShell posts
- Edwin Sarmiento’s blog
Happy Scripting! 🙂