SqlDev PowerShell 102: Working with Data and Variables

In my previous post, SqlDev PowerShell 101: Getting Started with PowerShell, I walked through how to configure and use PowerShell to run basic commands. This post will cover working with data and variables in PowerShell. Specifically, I’m going to demonstrate how to connect to a database (i.e. SQL Server) using Invoke-Sqlcmd, retrieve data, store the data in a variable, and work with that variable.

Note: Invoke-Sqlcmd does require the use of the sqlps module. If you don’t have it installed yet, check here for more information.

From a new PowerShell window, write the following command:

Invoke-Sqlcmd -Query "SELECT DB_NAME();"

PowerShell Working with Data: PowerShell_102_01

Tip: You can auto-complete “Invoke-Sqlcmd” after typing “Invoke-S” and hitting TAB on your keyboard.

What’s happening here? Well, because no database instance was specified, the cmdlet is connecting to the database on the local computer and running the query specified. “Column1” is returned just as it would be in SQL Server Management Studio (SSMS), because no column name was specified. You can see this by running:

Invoke-Sqlcmd -Query "SELECT DB_NAME() AS MyDatabaseName;"

PowerShell Working with Data: PowerShell_102_02

But what if you don’t have a local database instance, or you want to connect to another database? No problem. Just add the -ServerInstance parameter:

Invoke-Sqlcmd -Query "SELECT DB_NAME() AS MyDatabaseName;" -ServerInstance "."

“.” refers to the local, default SQL Server instance. If you want to connect to a named or remote instance, just replace this value with your server name.

Tip: Hit the “Up” arrow to retrieve the previous results, then just edit the command.

PowerShell Working with Data: PowerShell_102_03

So that’s all pretty easy so far, but honestly, if all I needed to do was run a simple query, I’d do it in SSMS. No, the real power of PowerShell is the .NET Framework. So let’s see this in action by assigning our results to a variable. A variable is defined using the dollar sign ($) and a name; defining a type is optional. Let’s do that now.

$DbName = Invoke-Sqlcmd -Query "SELECT DB_NAME() AS MyDatabaseName;" -ServerInstance "."

PowerShell Working with Data: PowerShell_102_04

In order to view the value stored in the variable, just append a new command to the previous line using a semicolon (;) and the variable’s name.

$DbName = Invoke-Sqlcmd -Query "SELECT DB_NAME() AS MyDatabaseName;" -ServerInstance "."; $DbName

PowerShell Working with Data: PowerShell_102_05

Optionally, you could also type the variable’s name and press Enter.


PowerShell Working with Data: PowerShell_102_06

Now let’s run a query that returns multiple columns:

$DbDetails = Invoke-Sqlcmd -Query "SELECT database_id, name, state_desc, recovery_model_desc FROM sys.databases WHERE name = 'master';" -ServerInstance "."; $DbDetails

PowerShell Working with Data: PowerShell_102_07

We didn’t define a data type, so let’s see what data type PowerShell assigned for us. To do this, we’ll use the GetType method:


PowerShell Working with Data: PowerShell_102_08

You can see that the variable is defined as a system object; specifically, it’s a data row. That’s important, because it determines how we’ll interact with the variable. For instance, what if we wanted to reference the value stored in the variable, not the variable as an object? You can do this by referencing the column stored in the object directly, i.e.


PowerShell Working with Data: PowerShell_102_09

Now let’s run the same query, but remove the database criteria from the WHERE statement:

$DbDetailsAll = Invoke-Sqlcmd -Query "SELECT database_id, name, state_desc, recovery_model_desc FROM sys.databases;" -ServerInstance "."; $DbDetailsAll

PowerShell Working with Data: PowerShell_102_10

Your results will vary, of course, but the important thing is that you should have more than 1 row returned.

Now let’s return just the database names again:


PowerShell Working with Data: PowerShell_102_11

Hopefully you’re starting to see how retrieving an array of values and accessing just a single attribute could be useful.

Let’s now iterate through our list of databases and perform an action. In this example, we’ll just iterate through each database and retrieve the count of tables. To do this, we’ll need to use a Foreach loop.

Foreach ($DatabaseRow In $DbDetailsAll) {$TableCount = Invoke-Sqlcmd -Query "SELECT COUNT(*) AS TableCount FROM sys.tables;" -ServerInstance "." -Database $DatabaseRow.name; $TableCount }

PowerShell Working with Data: PowerShell_102_12

This is the biggest statement we’ve written yet, so let’s take a minute to break it down.

We’re starting the loop by calling Foreach, then we’re specifying how we’re iterating through the array. As each row in the array comes into focus, we’re storing the row in the $DatabaseRow variable that we’ve just defined. Everything specified inside the curly brackets ({}) is executing for each row in the array. We’re then connecting to the local instance and changing the context of the database to be the value currently stored in $DatabaseRow. The results are stored in a new variable, $TableCount, which is then outputted to the screen.

It’s actually a pretty short piece of code, given all that it’s doing. But it’s difficult to understand which TableCount maps to which table, so let’s add that information to our output:

Foreach ($DatabaseRow In $DbDetailsAll) {$TableCount = Invoke-Sqlcmd -Query "SELECT '$DatabaseRow.name' AS DatabaseName, COUNT(*) AS TableCount FROM sys.tables;" -ServerInstance "." -Database $DatabaseRow.name; $TableCount }

PowerShell Working with Data: PowerShell_102_13

Woah, that’s not what we want! PowerShell is displaying the object type, not the value. In order to actually retrieve the value from an object variable, we’re going to store the database name in a string variable first, then reference that newly-defined variable.

Foreach ($DatabaseRow In $DbDetailsAll) {$DatabaseName = $DatabaseRow.name; $TableCount = Invoke-Sqlcmd -Query "SELECT '$DatabaseName' AS DatabaseName, COUNT(*) AS TableCount FROM sys.tables;" -ServerInstance "." -Database $DatabaseRow.name; $TableCount }

PowerShell Working with Data: PowerShell_102_14

Voila! Now we have a list of databases on a server and the associated count of tables in each database.

If you’re wondering why you’d ever actually use this PowerShell script, well… you probably wouldn’t. But it’s important to have these building blocks before going into more advanced concepts. In my next post, we’ll be creating a config table of SQL Server instances, connecting to each server, retrieving details about missing indexes, and writing those results out to disk. That should be a little more useful. 😉

Until then… happy scripting! 🙂

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Bookmark the permalink.

4 Responses to SqlDev PowerShell 102: Working with Data and Variables

  1. Pingback: (SFTW) SQL Server Links 30/08/13 • John Sansom

  2. Nice post… I am a huge fan of Powershell… look forward to following.

  3. Andre Ranieri says:

    Michelle – thanks for the great post! I’m a late bloomer to PowerShell and am finding your ground-up tutorials very useful in pushing my own knowledge level.


    Andre Ranieri @sqlinseattle

  4. Hi Michelle

    I am trying to run query using powershell against central management server. query is executing ok , but the issue is that when trying to store that output inside table everyting stored as string .
    Here is the code that i am using .

    # Get the list of Instances

    $instanceNameList = invoke-Sqlcmd -query “select name from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] where name=’servername,1520′” -serverinstance serverinstance
    # We’re looking for CMS groups with specific names (‘SQL Server 2008’, etc), so you’ll need to
    # make sure these match what you have in your environment.

    $results = @() #Initialze the Array
    # Populate the array with instance and DB information

    foreach($instanceName in $instanceNameList)
    {#$sqlversion=invoke-sqlcmd -query “exec sp_server_info 2″ -ServerInstance $instanceName.Name
    #$SQLVersion=”{0}” -f $sqlversion.attribute_value
    $results += Invoke-Sqlcmd -query “select trackingdate,servername,database_name,totaldata,dataused,DataFree
    from admin..trackdbgrowth” -ServerInstance $instanceName.Name

    # Since we’re only interested in the latest information, truncate the DBList table
    invoke-sqlcmd -query “use admin; truncate table trackdbgrowth;” -serverinstance serverinstance

    # Write the results of the above query to the DBList table
    foreach($db in $results) {
    # Since SQL Agent doesn’t handle PowerShell variables entirely well, we work around it
    # by assigning our variables a little backwards
    $tdate=”{0}” -f $db.trackingdate
    $sname=”{0}” -f $db.servername
    $dbname=”{0}” -f $db.database_name
    $tdate=”{0}” -f $db.totaldata
    $dused=”{0}” -f $db.dataused
    $dfree=”{0}” -f $db.DataFree

    $querytext=”insert into admin.dbo.trackdbgrowth (trackingdate,servername,database_name,totaldata,dataused,DataFree) values ( ‘$tdate’, ‘$sname’,’$dbname’,’$tdata’,’$dused’,’$dfree’);”
    invoke-sqlcmd -query $querytext -serverinstance serverinstance }

    Could you please suggest if we can store query output in same datatype that it is.
    Like for int it should sotred as int , for datetime it should stored as datetime.

    your much appriciated .

Leave a Reply

Your email address will not be published. Required fields are marked *