Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

A SQL Cmdlet a Day 3 Variables

, 7 Apr 2014 CPOL
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)

Share

About the Author

derekman9707
Database Developer
United States United States
David retired as a Sergeant with the Cape Coral Police Department after 22 years of service. His final 10 years of duty were as a database administrator and developer in the Administrative Services Division. He began his career with the police department in 1990 in the patrol division and worked various assignments until being promoted to Sergeant in 1998. Based on his education and experience David was assigned to Administrative Services in 2002 and was responsible for database administration, software integration, and development for public safety. David’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.
Follow on   Twitter

Comments and Discussions

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