HOW-TO use Powershell - Part 1

I was excited hear about powershell feature in SQL server 2008. Initially, it was not clear to me what it was, yet, I was enthused about learning something new. I imagined power shell would involve something in the command line. What happens after that was a complete mystery to me. Anyways, I managed to spend some extra time to learn about the powershell technology. I am going to split this article into multiple series. In the 1st part, I will cover how to work with variables and various helpful commandlets.

Powershell is an extended shell that allows us to manipulate objects and do various scripting tasks. It takes the adavantage of .NET. We will often run into CmdLet in powershell. A commandlet is a .NET object that exposes functionalities for us. For example, there is a cmdlet called "New-Variable" which can be used to declare variables.

To start running powershell type in "sqlps" in command line prompt. If you do not know what cmdlets are available for use then simply type in "get-alias". This handy little cmdlet can show you the shorthand for the available cmdlet. This will get you started so you can start exploring cmdlets. There is another cmdlet named "get-command" that lists all available cmdlets.

PS SQLSERVER:> get-alias

CommandType Name Definition ----------- ---- ---------- Alias ac Add-Content Alias asnp Add-PSSnapin Alias clc Clear-Content ...

When I started working with powershell, I struggled to find documentation on cmdlet. I looked in books online and it did not have much at that time. There were some references on cmdlets. Anyways, there is no need to despair. A cmdlet called "Get-Help" that can help you pull cmdlet related documentation from the command line. You are to provide the name of the cmdlet as the argument. Other useful arguments available for most cmdlets include "-detailed", "-full". Those flags allows you to pull very detailed information on the parameter. Amongst the, the "-full" flag is the most comprehensive. Also, if you type in "Get-Help *" then you can see a list of all available help topics. Powershell documentation reminded me of the manpages in unix computer and the RTM mantra.

Strings, variables, objects:

Declaring and working with variables is very simple. It can be accomplished using cmdlets such as "New-Variable", and "Set-Variable". Also, we can declare it like one would do it in scripting languages like perl, php.

PS SQLSERVER:> $rnd = New-Object "Random" PS SQLSERVER:> $rnd.Next() 778277127 PS SQLSERVER:> $str = "Me" PS SQLSERVER:> $i = 1

In the output above, we declared a variable namely $rnd using the Random class in .NET framework. The next line invoked the Next() method to generate the random number. The New-Object cmdlet can help us instantiate any .NET framework classes. However, keep it in mind that we have access to a subset of .NET framework classes. We can refer to any class that SMO can refer to. That's the only constraint I found here. In addition to building reference type variables, we can also create primitive types such as string and integer. The example above $str, $i does that respectively.

How do I know which variable is of which type? It's fair question due to the fact that there are no enforcement of type declaration. We simply call the method GetType() on the variable. See the output below.

PS SQLSERVER:> $i.GetType()

IsPublic IsSerial Name BaseType -------- -------- ---- -------- True True Int32 System.ValueType

There are special variables in powershell. Here is a list of them and what they do:

1. $_ - Captures the current object in the pipeline, used in script blocks, filters, and the where statement. 2. $Args - Contains an array of the parameters passed to a function from command line. 3. $Error - the object containing error information. 4. $Home - user's home directory. 5. $PsHome - Windows PowerShell installation directory.

This is all for the 1st part.