I wrote an article on using PowerShell to call stored procedures, but a friend sent me a few questions in email about how PowerShell works. I have assumed most people have tried it by now, but perhaps not, so I decided to write a few basic items on PowerShell for those getting started.
There is a great Stairway series on PowerShell from Ben Miller, which I enjoyed, but it goes a little quickly. I would defer to the Level 1 article for installing PowerShell (PoSh), and then the other ones for working with SQL Server. Some of the information is older, as the SQLPS module has been deprecated and dbatools is the recommended way to use PoSh and SQL Server, but it's a good series that will teach you something about programming against SQL Server. Highly recommended.
This article will cover some of the basics on how you write programs in PoSh, from the perspective of a DBA that is used to using T-SQL. We'll cover the basics of variables, objects, and pipelines.
Getting Started
Read Ben's Level 1 to install PoSh and set the execution policy. It also covers some basics of running cmdlets. I'm going to look at how I learned some things about PowerShell as a DBA.
First, working in PoSh is similar to how I work in T-SQL. If I write a few commands in SSMS, they get executed in sequence. Same with PoSh. However, most of the time I find myself at a command line trying things out before I assemble things into a program, like a stored procedure.
Variables
In T-SQL, we have to use DECLARE to specify a variable name and type. In the code below, we declare a variable, called @fullname, give it a type and assign it the value of "Steve".
DECLARE @fullname varchar(200)= 'Frank';
This is a strongly typed way of programming. In PoSh, we can create a variable anytime, just by using the $ prefix and giving it a name. We don't specify a type, and in general, all variables, are objects. That means they have properties and methods we can use.
If I repeat the same type of variable assignment above in PoSh, I'd do this:
$Fullname = "Frank"
I can tell this is an object, if I run this through Get-Member, which will let me know the properties and methods. Here is the call and the result.
$Fullname | Get-Member
TypeName: System.String
Name MemberType Definition
---- ---------- ----------
Clone Method System.Object Clone(), System.Object ICloneable.Clone()
CompareTo Method int CompareTo(System.Object value), int CompareTo(string strB), int IComparable.CompareT
Contains Method bool Contains(string value)
CopyTo Method void CopyTo(int sourceIndex, char[] destination, int destinationIndex, int count)
EndsWith Method bool EndsWith(string value), bool EndsWith(string value, System.StringComparison compari
Equals Method bool Equals(System.Object obj), bool Equals(string value), bool Equals(string value, Sys
GetEnumerator Method System.CharEnumerator GetEnumerator(), System.Collections.IEnumerator IEnumerable.GetEnu
GetHashCode Method int GetHashCode()
GetType Method type GetType()
GetTypeCode Method System.TypeCode GetTypeCode(), System.TypeCode IConvertible.GetTypeCode()
IndexOf Method int IndexOf(char value), int IndexOf(char value, int startIndex), int IndexOf(string val
IndexOfAny Method int IndexOfAny(char[] anyOf), int IndexOfAny(char[] anyOf, int startIndex), int IndexOfA
Insert Method string Insert(int startIndex, string value)
IsNormalized Method bool IsNormalized(), bool IsNormalized(System.Text.NormalizationForm normalizationForm)
LastIndexOf Method int LastIndexOf(char value), int LastIndexOf(char value, int startIndex), int LastIndexO
LastIndexOfAny Method int LastIndexOfAny(char[] anyOf), int LastIndexOfAny(char[] anyOf, int startIndex), int
Normalize Method string Normalize(), string Normalize(System.Text.NormalizationForm normalizationForm)
PadLeft Method string PadLeft(int totalWidth), string PadLeft(int totalWidth, char paddingChar)
PadRight Method string PadRight(int totalWidth), string PadRight(int totalWidth, char paddingChar)
Remove Method string Remove(int startIndex, int count), string Remove(int startIndex)
Replace Method string Replace(char oldChar, char newChar), string Replace(string oldValue, string newVa
Split Method string[] Split(Params char[] separator), string[] Split(char[] separator, int count), st
StartsWith Method bool StartsWith(string value), bool StartsWith(string value, System.StringComparison com
Substring Method string Substring(int startIndex), string Substring(int startIndex, int length)
ToBoolean Method bool IConvertible.ToBoolean(System.IFormatProvider provider)
ToByte Method byte IConvertible.ToByte(System.IFormatProvider provider)
ToChar Method char IConvertible.ToChar(System.IFormatProvider provider)
ToCharArray Method char[] ToCharArray(), char[] ToCharArray(int startIndex, int length)
ToDateTime Method datetime IConvertible.ToDateTime(System.IFormatProvider provider)
ToDecimal Method decimal IConvertible.ToDecimal(System.IFormatProvider provider)
ToDouble Method double IConvertible.ToDouble(System.IFormatProvider provider)
ToInt16 Method int16 IConvertible.ToInt16(System.IFormatProvider provider)
ToInt32 Method int IConvertible.ToInt32(System.IFormatProvider provider)
ToInt64 Method long IConvertible.ToInt64(System.IFormatProvider provider)
ToLower Method string ToLower(), string ToLower(cultureinfo culture)
ToLowerInvariant Method string ToLowerInvariant()
ToSByte Method sbyte IConvertible.ToSByte(System.IFormatProvider provider)
ToSingle Method float IConvertible.ToSingle(System.IFormatProvider provider)
ToString Method string ToString(), string ToString(System.IFormatProvider provider), string IConvertible
ToType Method System.Object IConvertible.ToType(type conversionType, System.IFormatProvider provider)
ToUInt16 Method uint16 IConvertible.ToUInt16(System.IFormatProvider provider)
ToUInt32 Method uint32 IConvertible.ToUInt32(System.IFormatProvider provider)
ToUInt64 Method uint64 IConvertible.ToUInt64(System.IFormatProvider provider)
ToUpper Method string ToUpper(), string ToUpper(cultureinfo culture)
ToUpperInvariant Method string ToUpperInvariant()
Trim Method string Trim(Params char[] trimChars), string Trim()
TrimEnd Method string TrimEnd(Params char[] trimChars)
TrimStart Method string TrimStart(Params char[] trimChars)
Chars ParameterizedProperty char Chars(int index) {get;}
Length Property int Length {get;}As you can see at the top, this is a string type. That means all the methods I can use for strings, like ToLower, ToUpper, etc. are available.
Likewise, I can all a common command, like Get-Module. This will return me the items installed on my system.
PS> Get-Module
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 0.0 chocolateyProfile {TabExpansion, Update-SessionEnvironment, refreshenv}
Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Script 2.0.0 PSReadline {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Remove-PSReadLineKeyHan... Manifest 15.0 SQLPS {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIpI can assign the results of this to a variable like this:
$Modules = Get-Module
If I run that through Get-Member, I find something different for the type:
PS>$Modules | Get-Member TypeName: System.Management.Automation.PSModuleInfo
No change in how I declared the variable, which is much different than T-SQL, but also both fantastic and dangerous. I find object mismatches all the time as I write code, because I'm thinking of these are variables, not objects.
Why Do You Care?
I don't want to delve too deep into the whole why objects matter. Suffice it to say that you should understand that using parameters to store values and to pass into other cmdlets or functions, means that you need to get objects that are compatible.
You mainly care because some of the functions you'll call require the object type to match, as there isn't always a way to convert one object to another.
The good thing is that PoSh will often try to use a property, like Name, in the object if it can as the input for another function.
Reassigning Variables
I can also reassign a variable to a new type, and PoSh handles this. Look at the code below, where we have a string that we then assign an integer as its value.
PS>$Fullname
Frank
PS>$Fullname = 45
PS>$fullname |Get-Member
TypeName: System.Int32This is good, in that we can easily reuse variables if we want, but it's bad in that we can cause ourselves issues.
The bottom line is be careful of how you use variables in scripts and try to use new ones for each task, and use descriptive names.
Running Code
We don't have an IDE like SSMS for PowerShell, Instead, we have a few options for how we write and run code. Interactively, we can run code from a command line or an IDE like VS Code. From my start menu, I can easily run a PowerShell command line by picking that item.
This opens a command like window, though the default is blue so that you know this is PoSh, not a normal DOS-style window. The prompt also starts with PS.
I
can also open a normal "command prompt" that runs cmd.exe. From there, I can run "powershell" to get into the Powershell prompt.
If I open VS Code, I also have a terminal at the bottom, which defaults to PoSh.
From any of these places, I can run some interactive code in PoSh. For example, I can assign a series of variables:
PS C:\Users\frank> $name = "Frank" PS C:\Users\frank> $age = 40 PS C:\Users\frank> $title = "DBA" PS C:\Users\frank> $name + " is " + $age + " and works as a " + $title Frank is 40 and works as a DBA PS C:\Users\frank>
PoSh takes care of converting the $age to a string in this case, something that T-SQL doesn't do. This is because T-SQL would try to convert the strings to ints.
We can also put code in a file. If I take those lines and put them in a file in VS Code, I can save this to my file system. The default extension is .ps1, so I'll use that to save this code as frank.ps1. I then run this by calling the filename in PoSh. Note that I need to specify this is in the local folder with a ".\".
Note, if you get an error here, it might be because you haven't set the execution policy. You can see how to do that in Ben's first Stairway article.
From a cmd.exe CLI, we can also run code by running powershell.exe and passing in the name of our script as a parameter.
Getting Oriented in PoSh
There are a couple things to think about in PowerShell that I find handy. First, this is a batch language, much like you might write a .cmd or .bat file in a command prompt. Your code in a file executes line by line. Keep that in mind.
Second, much of PoSh is built around functions, called cmdlets. These are of the Verb-Noun category. So there are the verbs, some of which are:
- Add
- Copy
- Find
- Get
- Move
- Set
- Show
There are others listed on the Microsoft Docs site. This is what software companies, and you, should follow for your own cmdlets.
The Noun part of the cmdlet is descriptive, like Get-Member, or Get-Service. Notice these are singular, unlike the way most of us name our tables. We can guess names also, as the command line interface (CLI) has tab completion. If I type "Get-S" in my generic lab vm, I can hit tab and I'll see:
Then I can hit Tab and I'll see:
I can keep doing and I'll go through Get-SecureBootPolicy, Get-Service, and more. If I keep going, I'll get back to Get-ScheduledJob.
This is great, because I often can't quite remember a specific name, so I can tab through what I know, though to be fair, as I've added the Azure modules to my system, Get-A is fairly useless.
The other thing to be aware of us that PoSh uses the PascalCase for the words. Notice how the first letter of each word in the cmdlet is capitalized.
Lastly for this article, the Get-Member and Get-Help cmdlets are invaluable. We've seen Get-Member above, which is useful when you can't remember a property or method name. Get-Help is what you call when you don't know how to use something. You can pass in the cmdlet as a parameter and get the help back. 
Mike Fal has a good article on adding help to your own cmdlets, and you should do that.
That's enough to get your oriented, let's make one practical little piece of code.
Our First Useful Script
While we wrote a script above, it wasn't very useful or helpful. It's good practice, but it doesn't serve much of a purpose. Instead, let's do something useful.
If I run Get-Service, I get a long list of services. On most Windows machines, this is dozens of things running, most of which I don't care about. However, because the output of this can be saved into a variable, we can do things with it.
For example, I may run this:
$service = Get-Service
Now I have all the services on my machine in the variable with their name, status, etc. One useful cmdlet that I often use is Where-Object, which is like a WHERE clause in T-SQL. With this, I can filter things out. I'll show the code, then explain it.
$service | Where-Object {$_.Name -like "SQL*"}I am using a pipe here, the | symbol. This essentially passes one object as a parameter to another cmdlet. In this case, I pass the $service object to Where-Object. My filtering takes place in the braces, {}, where I can specify how I filter. Here I want to like for a wildcard match, like, with a string. As with T-SQL, an asterisk is use for any number of characters.
The notation, $_., is used to specify the object passed in. In this case, $service is used for $_. The .Name specifies the property I want to do a comparison on. In this case, the name property. When I do this, I get just those services that start with SQL in the name. I could use the description, status, or other properties if I wished.
Since I'm using a pipe here, I can actually skip the variable and pipe the output from one cmdlet to another. Let me create a file that looks like this:
Now I can save this and run it from the PoSh CLI. I do that and I just see my SQL Server services.
While this might not feel super useful, being able to run this script and get my service status from the cmdline, without filtering things out manually or scrolling through the Services applet is handy.
Note: The comparison operators are very un-intuitive in PoSh. Instead of the <>=, or ==, we have -gt, -lt, -eq, and -ne. In another article, I'll cover more of these.
Summary
This article is a very basic introduction to PowerShell. We looked at variables as objects, how to run code, and build a short script. This gives you a starting point for working with the language in general. In the next article, I'll look at more SQL Server specific tasks.
I know that this covered a lot of information, and it can be confusing. I found that I was moving in circles sometimes when learning PoSh, getting fragments of what I needed, and not understanding some of what I learned, but I slowly worked my way through and practiced a bit, trying to build useful scripts for myself.
Follow along, try out some code, and let me know what's confusing, or what you want to learn.
