PowerShell in SQL Server 2008 – Part II

Hello Geeks,

You might have seen in my previous blog, about the introductory part of Windows PowerShell. If not, then you can browse over here;

In this post I will let you know the basic commands, operators and variables that are used in the PowerShell.

As I have said earlier that these cmdletsuse a verb-noun naming convention, so it becomes very easy to understand what they are actually doing. The pipes used in PowerShell are being used to connect objects from one cmdlet to another. Cmdlets are also frequently aliased. One more advantage of using cmdlet is that, they are not case sensitive.

Cmdlet Commands are as follows:

  • Get-ChildItem: This PowerShell cmdlet is being used to view the contents of the current directory. As we use the command like “dir” in DOS OS and UNIX users are familiar with “ls” command. So the Alias of Get-ChildItem is dir and ls.
  • Get-Location: This PowerShell cmdlet returns the path that you are currently working upon.

1_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

  • Get-Help Get: This PowerShell cmdlet is being used to see all the commands which has a prefix “get”. This is a help command which can be used to find the details about any other cmdlet. For example, Get-Help Get-Location will let you know about the Get-Location command in a brief.  
  • Clear-Host: This PowerShell cmdlet is being used to clear the screen. The Alias is ‘Clear’. 
  • Set-Location: This PowerShell cmdlet is being used to set the current location of the user.  

2_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

  • Get-Date: This PowerShell cmdlet will let you know about the system date, day and time.

3_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

Well if you want to be more specified just about the date or time, then use:

4_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

  • Get-Service: This PowerShell cmdlet will let you know about the services with their Name and Status. This will become more clear when we use it with pipe, i.e., input the results to the next cmdlet. 

5_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

The get-service will input all the services to the cmdlet where-object and then to the sort-object.  

  • ForEach-Object: This PowerShell cmdlet will performs an operation against each of a set of input objects. The Alias is ‘%’.

6_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

   

This command accepts an array of integers, divides each one of them by 1024, and displays the results. 

  • Where-Object: This PowerShell cmdlet filters objects based on conditions. The Alias is ‘?’.
  • Select-Object: This PowerShell cmdlet pipes only the specified properties. The Alias is ‘Select’.

7_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

This command displays a list of processes. Only the name, ID and working set (WS) properties of the processes are displayed. 

  • Sort-Object: This PowerShell cmdlet sorts the objects. The Alias is ‘sort’. 
  • Tee-Object: This PowerShell cmdlet saves the command output in a file or variable, and displays it in the console. 

8_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

This command gets a list of process running on the computer and sends the results to the file. Since the second path is not being mentioned, the result will be displayed in the console.

Comparison Operators:

  • -lt: Less than
  • -le: Less than or Equal to
  • -gt: Greater than
  • -ge: Greater than or Equal to
  • -eq: Equal to
  • -ne: Not equal to
  • -like: Like wildcard pattern matching

 How to use the variables:

The PowerShell variables are being declared by preceding the variable name with the dollar sign ($) character.  Variables are real objects of a particular type, and that data type can be created by preceding the variable name with the data type in brackets ([]).

For example, a variable called “$value” that’s an integer object can be created and set to a value of 5 as follows:

PS C:\> $value=5

If it is a string object with value of ‘5’ can be created as follows:

PS C:\> [string] $value = ‘5’

Variables can also store group of objects, such as an array:

PS C:\> $value = 1,2,3,4,5

To pick the fifth value in an array, perform the command:

PS C:\> $value[4]

9_SQL_Server_PowerShell_in_SQL_Server_2008_Part_II

Well, this might be enough for this post. In the next blog post, I would like you to tell about how to Create the Scripts, Run them and much more. Stay tuned!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “PowerShell in SQL Server 2008 – Part II”

  1. Nice article Piyush

    It is my understanding that this will only work if the remote machine also has powershell installed, which is why t’s a shame how most companies are still in Win2k3 enviroments,

    Powershell is a powerfull tool and saves lot’s of time for managing servers.

Leave a Reply

Your email address will not be published.