Making SQL sense

+44 (0)20 3051 3595 info@coeo.com
coeoClose

Making SQL sense

+44 (0)20 3051 3595 info@coeo.com

Careers

We're looking for people who share our commitment to excellence in Microsoft's data platform to join us consultants working on exciting business intelligence, analytics, and SQL Server projects on-premises and in the cloud.

Getting started with PowerShell (PoSH)

The Coeo Blog

This is a quick start guide to PowerShell and by the end of reading it, you should be able to be write your own scripts and explore the language confidently.

PowerShell

PowerShell is a scripting language which was released way back in 2006 and is built upon the Microsoft .NET Framework. It looks very familiar to anyone who has used a .NET language , but equally any object-oriented language. Thankfully those without any development knowledge can also very quickly pick up the fundamentals of PowerShell as it is also very intuitive - so do not worry!

PowerShell has gone beyond the problems associated with its early release, such as lacking functionality and performance concerns, to the point where it can now be picked up with ease. PowerShell version 3.0 onwards is what you want to be using.

Finding PowerShell

Originally PowerShell 1.0 was installed as an optional feature of Windows, however from PowerShell 2.0 onwards, it became part of the operating system base build.

By default, PowerShell is installed to the following locations, regardless of version.

64-bit

  • %SystemRoot%\system32\WindowsPowerShell\v1.0\

32-bit

  • %SystemRoot%\syswow64\WindowsPowerShell\v1.0\

 

There are two variants of PowerShell included, which are the 32-bit version and the 64-version. The 64-bit version is preferred and you should only use the 32-bit version if you experience issues.

There are also two executable (.exe) files: one named powershell.exe and the other named powershell_ise.exe. If you are performing development work, then the latter, which is an Integrated Scripting Environment (ISE) is going to be the most appropriate choice, as it provides a friendly interface.

If you've developed a script and want to run it without using the ISE, then you can run it within a simple console which is what powershell.exe provides you with.

Using PowerShell

Let’s start by opening PowerShell or PowerShell_ise by navigating to the folder listed above, or alternatively locating the shortcut from your operating system Start menu.

This first thing to note about PowerShell is that the familiar old black and white console of MS-DOS has made way for a new white and blue console.

The console will display the current directory prefixed by PS to indicate that this is a PowerShell console.

The next lessons will introduce you to some common features and tasks you can perform using PowerShell.

Lesson 1. Variables

Variables in PowerShell are prefixed with a dollar $. A variable can be assigned a value with a statement such as $name = "Jon" , however we will look first at the system variable $PSVersionTable.

The system variable $PSVersionTable contains version information about PowerShell and is a good place to start, as it is more complicated than a simple string variable.

Typing the variable in to the console and hitting enter will produce output like the following shown in Figure 1.

Figure 1. $PSVersionTable

PSVersionTable

 

In the results above, the PowerShell version is stored as PSVersion and has a value of 5.1.15063.726 which is major version 5. It's important to know the version that you are using, as changes to functionality can impact how you write your script.

Lesson 2. Objects

A simple string variable is an object and everything can be thought of in terms of objects. When moving to an object-orientated language, it is this change that baffles a lot of users.

For example here are three representation of a Person. We start with a simple string object variable, to a more complex object variable with string properties.

 

  • $Person0 = "Jon";
  • $Person0 | Format-Table;

 

  • $Person1 = New-Object PSObject;
  • $Person1 | Add-Member Noteproperty Name -Value "Jon";
  • $Person1 | Format-Table;

 

  • $Person2 = New-Object PSObject;
  • $Person2 | Add-Member Noteproperty Name -Value "Jon";
  • $Person2 | Add-Member Noteproperty Age -Value 21;
  • $Person2 | Format-Table;

 

If we look back at the output of the variable $PSVersionTable, it showed multiple lines which could have been stored as a single string of characters. However, its output is actually stored as a Hashtable object which is a collection of name and key value pairs.

All of these keys can be shown by accessing the Keys property on the object by writing $PSVersionTable.Keys. There are many other properties we can access on that object too, including:

  • $PSVersionTable.Item('PSVersion')
  • $PSVersionTable.PSVersion

The output for the latter is shown below in Figure 2.

Figure 2. $PSVersionTable.PSVersion

PSVersionTable.PSVersion

 

Although the value is the same as that shown in Lesson 1, the output is not formatted exactly the same. The reason for this is that the PSVersion value is not a simple string but is instead an object made up of several further properties which we deduce by the column headings: Major, Minor, Build and Version. These properties can be accessed individually as shown in Figure 3.

Figure 3. PSVersionTable.PSVersion.Parts

PSVersionTable.PSVersion.Parts

 

Lesson 3. Methods

A method is an action that can be performed by an object. As PowerShell is built on the .Net Framework, there are many methods that are available as part of the standard .Net object hierarchy.

An example of this is the ToString() method which converts the object to its string representation.

If the ToString() method is used on $PSVersionTable.PSVersion, its value is returned as a string type. This matches that shown in Lesson 1: behind the scenes the PSVersion property (returned as part of $PSVersionTable object variable output) was actually returned as a string. Figure 4 below shows what the output looks like as a string.

Figure 4. PSVersionTable.PSVersion.ToString

PSVersionTable.PSVersion.ToString

 

Lesson 4. Cmdlets

A Cmdlet is piece of functionality wrapped up in a single command. PowerShell Cmdlets can be very useful for providing out-of-the-box functionality.

Running Get-Command will list all the Cmdlets that are loaded into the console.

As an example, lets run the following to get a list of all the commands that have the word "help" in their the name.

  • Get-Command -Name *help*

There are two things to note here.

  1. The naming convention starts with a verb (used to describe an action) and ends with a noun (the action). There a standard set of verbs for PowerShell, which can be listed using Get-Verb. So coupled with an action you want to perform, you can guess the command names in many scenarios. For example, I want to get all the commands loaded into the console. Get-Command would be an obvious choice for such a Cmdlet.
  2. A variable has been passed using -Name *help* and in this case accepts the wildcard character *. Variables do not need to be passed by name and could be passed by position alone, however commands passed by name are more readable.

Figure 5 below shows the output from this command.

Figure 5 Get Command Help

GetCommandHelp

 

Lesson 5. Help Cmdlet

The Cmdlet Get-Help can be used to find out more information about commands at varying levels of detail. If you find that more information is needed about a Cmdlet then Get-Help is going to be where you need to start.

  • Get-Help Get-Command
  • Get-Help Get-Command -Detailed
  • Get-Help Get-Command -Full
  • Get-Help Get-Command -Examples

Asking for examples by specifiying -Examples, can be very useful if you are unsure of how a Cmdlet should be run.

You can use the Get-Member Cmdlet as shown in Lesson 6 if you want to list the actions or properties for a particular object. Alternatively, Intellisense within PowerShell ISE will list them after you type . after the object.

Lesson 6. Pipe

A pipe | is a way to pass an object from the left of the piple to the right of the pipe. The next examples shows how the output of the Get-Date Cmdlet is passed to the Get-Member Cmdlet.

  • Get-Date | Get-Member

By performing Get-Member against Get-Date a list or properties and methods are returned for the Get-Date object.

Lesson 7. Modules

A module is collection of Functions and code packaged together. It is a way for code to be logically grouped and distributed. Modules loaded into your PowerShell session, can be listed by running the Cmdlet Get-Module. One such module is called SqlServer and this provides Cmdlets relating to the administration of SQL Server.

A module can be installed on your local computer by using the Install-Module Cmdlet.

  • Install-Module SqlServer

A module can be imported/loaded into your PowerShell session by using the Import-Module Cmdlet.

  • Import-Module SqlServer

 

Note that from PowerShell version 3.0, a module is imported automatically when any Cmdlet or Function within that module are used.

PowerShell modules are made freely available and can be reviewed at the following Microsoft website:

https://www.powershellgallery.com/

Never miss an opportunity to learn - sign up to receive our latest eBooks, training courses and more: 

New Call-to-action

Subscribe to Email Updates