PowerShell Week at SQL University – Post 5

In the previous posts we’ve just been poking around with PowerShell and trying to make the examples something that actually means something to a SQL person whenever we ca.  There are quite a few more language constructs that we need to cover but we have enough info to start recouping the time we’ve already invested.  Now it’s time to do one of those tasks that I just love to do with PowerShell.  We’re going to loop.  And we’re going to loop in a way that’s far easier than in any part of the SQL language.

We’re going to cover 3 different sources for our loop that are the most common for DBAs to use: table, text file, Registered Server/Central Management Server.  After that we’re going to do a double loop and then it’s time for you to find something to do with these.

Text File

In this chunk of code we’re just going to read from a simple text file from our local hard drive and then loop through the instances in that file one at a time.  You put one instance on each line of the text file.  Don’t put it in quotes unless you’re using a non-standard port number.  This is the easiest method because each “row” that comes out of the text file only has one property.  We’ll find out why that’s important in the next example.  In the meantime setup your text file and test it by running just this: Get-Content C:\PowerShell\AllInstances.txt.

foreach ($Instance in Get-Content C:\PowerShell\AllInstances.txt)
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  object_name ,
        counter_name ,
        instance_name ,
        cntr_value ,
  FROM sys.dm_os_performance_counters"


Database Table/ Query

In this example we could be reading rows out of a table or running a more complex query to determine the list of databases that we want to run out query against.  This may actually seem easier to most database people and it is.  We’ve got a centrally located table and we can just look at it and know that we can change the first query to select a list of databases from somewhere else.  But there’s a really important thing to know if you swap out the query.   This approach is sending one usable property (column) to the foreach loop and it’s called “name”.  If you change the query and the column ends up being called database_name, you’re going to have to change $($db.name) to be called $($db.database_name).  Otherwise you’re going to loose a lot of hair and get really ticked after about 20 minutes like I did!

foreach ($db in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
Invoke-sqlcmd -Query 'SELECT *
  FROM sys.dm_exec_procedure_stats' -ServerInstance  Win7NetBook -Database $($db.name)


Registered Servers/ Central Management Server

Before you get started looping through your Registered Servers you’ll need to run this: Import-Module Agent if you want to do this exact example.  What we’re looking for here is all the jobs that have failed in the last 3 days in our “QA” group of servers.  This example should be easy enough for everyone to tweak on their own.  If you get stuck just remember to do Get-Help -Full Set-AgentJobHistoryFilter
(Huge thanks to Chad Miller for helping me put together this demo so that it would be a fast one to run!)

$filter = Set-AgentJobHistoryFilter -startDate $(get-date).AddDays(-3) -endDate $(get-date) -outcome 'Failed'
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
Get-AgentJobHistory $RegisteredSQLs.ServerName $filter | where-object {$_.StepID -ne 0}


Double Loop

This has to be one of my favorite PowerShell scripts of all time (so far).  I had to run a query against every database in a group of over 10 servers.  I’ve changed this one around a little but I’m sure you’ll find a use for it!  (Think permissions.)

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
    foreach ($DBName in invoke-sqlcmd -query "SELECT name
  FROM sys.databases WHERE name in ('AdventureWorks',
            ) " -database master -serverinstance $RegisteredSQLs.ServerName )
                    invoke-sqlcmd -query 'SELECT *
        FROM sys.dm_db_index_usage_stats' -ServerInstance $RegisteredSQLs.ServerName -database $DBName.name
            } #EndOfTheFoundDatabasesLoop
} #EndOfTheRegisteredServerLoop



Try out each of these methods and think up something you could use this for.  If you hit on something that save you some clicking around in SQL Management Studio please mention it in the comments.

Please Share This:

You may also like:

8 Responses

  1. Thanks for this intro to PowerShell and SQL Server. Over the past week I reviewed your material from last year as well as what you prepared for this semester. This opporuntity motivated me to actually begin focusing on adding Powershell as one of the tools in my DBA war chest. Due to your info I now realize that I have three ways to access powershell on my servers and how to use powershell for more than just a “dir” command. I am probably not even 1% proficient in it yet, but you have given me the tools to at least begin using the tools in a productive manner. Can’t wait for the week 2 later this semester.

  2. I had to modify the Database Table/Query script to run on my system

    I changed the first $db after the first curly brace to be $db.name. Without adding the .name suffix I only received the listing of DB names. When I added the suffix I actually executed the embedded query.

    Sounds bizarre to me, but thats my story and I’m sticken to it.

    Any ideas?

    1. Tom,
      Thanks for pointing that out to me; I have corrected that error. I explain the reason more in the next post but the short version of the difference is that $db means ‘give me the whole db [object] and everying that you know about it’ whereas $db.name means ‘Give me the contents of the “name” property of the $db object’.

  3. So, to make sure I understand the basic syntax of the foreach loop, it’s:

    foreach ($Variable in )


    Why don’t the last two loops have the “$Variable;” bit in there? Is that only required when using Invoke-SQLCmd?

    This is cool. I feel like I’m actually beginning to understand what I’m looking at. Thank you!

    1. Oh, great question. It’s not required at all actually. I just put them in there to use as a PRINT statement to let you know where it was at. You can take them out if you like. I will be going over that in today’s post.

  4. Very cool. Since the CMS keeps a table ([msdb].[dbo].[sysmanagement_shared_registered_servers_internal]) with all the registered servers on it, can you use PowerShell to loop through that table in order to do stuff with it?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner