Finding What You Need in PowerShell

In the last article, PowerShell Basics for DBAs, we talked about how PowerShell is organized as a set of drives that are accessible through psdrive. We also talked about cmdlets and how they take parameters. This time we’re talking about help, get-member, and format-table. These are going to be invaluable in your day to day PowerShell work. So let’s go ahead and get started.

One of the main ways you’ll learn to work with cmdlets is by using help. If you type help by itself at the prompt you get help about help. However, if you type help followed by a specific cmdlet, you’ll get help about that object like this:

>help get-service

SQL Server PowerShell Cmdlets: help get-service

If you look at the bottom of the above screenshot you’ll see in the ‘Remarks’ section that you can pass parameters to help. Sifting through the examples of each cmdlet is one of the best ways to learn about the different uses and functionality of it. Besides running help on a specific cmdlet, you can also search for cmdlets to learn about what’s available in PowerShell. You can run wildcard searches like this:

>help get*

This will show you all the cmdlets that start with ‘get’.


Probably the most useful cmdlet for getting output to the screen is format-table. Its meaning should be clear; it tells PowerShell to format the output as a table to the console. Let’s take a look at how this works. Type the following at the prompt:

>get-service | format-table

Here’s what you’ll get:

SQL Server PowerShell Cmdlets: get-service format-table

Funny, it looks just like it did above when you only typed ‘get-service’, doesn’t it? That’s because the default output type for get-service is format-table. So why did I use it as an example then? Well, because along with format-table being the default output, the default columns of that table are Status, Name, and DisplayName. What if you wanted to see other information about the services? This s where format-table comes into play. With format-table you can define the columns you want to see in your output because let’s face it, the default output may not be what you’re interested in. Let’s take a look at how to get different information about the services:

>get-service | format-table MachineName, Name, Status, CanStop –auto

You’ll get this in return if you run it on your local box:

SQL Server PowerShell Cmdlets: get-service format-table

So this is pretty easy to understand. You use get-service to get a list of services, and pass the output to format-table and then list the columns you want to be displayed. The ‘-auto’ parameter tells format-table to adjust the width of the columns based off of the data instead of a fixed width.

Without it the output would look like this:

SQL Server PowerShell Cmdlets: get-service format-table

See, without the ‘-auto’ parameter the output stretches across the page which makes it harder to stay on the same line as you cross the screen. So using ‘-auto’ is always desirable. That’s not all you can do with format-table, but it’ll do for now.


Of course now the big question is how do you know which columns are available in the above example? I mean, where did I get the new columns to display? The answer is get-member. PowerShell is a .Net-based language so every object in the pipeline is an object. And in .Net, objects have members. To put it succinctly, an object’s members are just the methods and properties that the object supports. Let’s take a look at an object’s members and you’ll see what I mean. Since we started with get-service, we’ll use it here too:

>get-service | get-member

This will yield:

SQL Server PowerShell Cmdlets: get-service get-member

In the output above, methods can be thought of as functions, or verbs. A method is something you can do to an object. Looking at some of the methods, you can Start, Stop, and Pause a service. We’ve already seen methods in action in the first article (PowerShell basics for DBAs). Properties are the pieces of information you can view about an object. And in our example, properties translate directly into columns for our format-table. You can see the properties that I chose for my format-table above are in the property list. How about another example real quick? This time we’ll do get-process because it has lots of properties to choose from.

>get-process | get-member

SQL Server PowerShell Cmdlets: get-process get-member

I can’t show you all the properties because I wanted to show you some methods too, but you’ll get a good idea here. Starting with a glance across all the methods (remember, these are things you can do to each process) you see you can kill, start, and close processes. And as for properties, you can see the properties you can use as columns in your format-table. Using a combination of help and get-member are 2 of your biggest tools when working with PowerShell. I’ve been using PowerShell for years now and I live in get-member constantly. And the members that come up in the list will change depending on the object. And that makes sense because if you look at the members for a string, you won’t have the same types of things you can do as you could for an integer or even a date. There’s more to say on all of these topics and we’ll cover them in future articles.