Click here to Skip to main content
Click here to Skip to main content

A SQL Cmdlet a Day 3 Variables

, 7 Apr 2014
Rate this:
Please Sign up or sign in to vote.
As with any scripting language PowerShell supports variables, but unlike many languages declaration and assignment is streamlined.  One of the coolest things about PowerShell variables is that data type assignment is automatic based upon the data type value.  Variable declaration is simply done by p

As with any scripting language PowerShell supports variables, but unlike many languages declaration and assignment is streamlined.  One of the coolest things about PowerShell variables is that data type assignment is automatic based upon the data type value.  Variable declaration is simply done by prefixing a “$” to the variable name.  The below statement creates a variable called “$var” and does an inline assignment of the string value “Hello World!!”.

$var = “Hello World!!”

By calling the variable within your command shell it will display the variable value.

image As I mentioned data type assignment is based on the variable value so in this case we don’t need to formally assign a data type.  The data type can be retrieved by calling the GetType() method of the variable. $var.GetType()

image To get a list of all members available we can pipe the $var variable to a Get-Member cmdlet:

$var | Get-Member

imageTo get more information on “piping” you can look at the second blog in this series here.

Since the data type of this variable is a string we cannot perform operations on it that are not supported on this type, such as incrementing the variable by one:

$var ++

image

But we can easily change the variable type by assigning a numeric value.

$var = 1

$var ++

$var.GetType()

image

Again, these examples and concepts are pretty straight forward so let’s dial it up and take a look at working with arrays.  The only difference in creating an array is in the syntax of the value assignment.  The below cmdlet creates a variable called $Computers and assigns an array of values.  The type is displayed by calling the GetType method and then the values are displayed by using the zero based index.

$Computers = @(“Server1″, “Server2″, “Server3″)

$Computers

$Computers.GetType()

$Computers[0]

$Computers[1]

$Computers[2]

$Computers[2] = “Server4“

$Computers[2]

 

image

Pretty kewl right?  But what does this have to do with using PowerShell variables with SQL?  Glad you asked!!  We’ll start with a simple example of creating a variable that will be assigned the value of the localhost default instance,  The first thing I’ll point out is rather than a direct assignment I am using New-Object command and specifying the type:

$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)  -argumentlist “localhost”

With the assignment complete we can now call the properties or methods available:

$server.Databases

image

This effectively demonstrates how PowerShell can be used to easily create variables of SQL objects and return information about them, but we want to be able to programmatically administer SQL right?  Consider the common and simple scenario of changing the recovery model of a database.  Obviously this can be done using SSMS, but what about scripting this out in PowerShell.  SIMPLE!!  The previous cmdlet demonstrates creating a variable to hold the localhost default instance and query its database, but this same variable can also be used to change the recovery model of one or all of the associated databases.  The below cmdlet calls the TwitterSearch database and pipes the output to a Select query to show the name and RecoveryModel of the database.

$server.Databases[”TwitterSearch”] | Select name, RecoveryModel

image

Changing the RecoveryModel is as simple as calling the property from our variable and assigning the value and then calling the Alter method to apply the change:

$server.Databases[”TwitterSearch”].RecoveryModel = “Simple”

$server.Databases[”TwitterSearch”].Alter()

$server.Databases[”TwitterSearch”] | Select name, RecoveryModel

image

This hopefully provided some useful insight into using PowerShell variables to query SQL information and apply object settings.  All posts in this series have worked directly with PowerShell from SSMS so the next post will outline using the the Windows PowerShell Window to import the SQLPS module.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

derekman9707

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 8 Apr 2014
Article Copyright 2014 by derekman9707
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid