Click here to monitor SSC
Phil Factor

PowerShell SMO: Just Writing Things Once

19 July 2012

Sometimes, you can tire of writing the same PowerShell code once again. After this happened to Phil whilst keying in an SMO pipeline to access databases, it occurred to him that he should have only one pipeline to access databases in PowerShell: one reusable pipeline.

In this article, I'll be showing you how to perform a variety of jobs on databases in a number of servers. Some involve simple things like getting database properties, but others involve executing SQL in all databases, or getting entire database-build-scripts. Generally, they create a single report for all your servers and databases that can then be imported into Excel or Word. You should be able to build on these examples to create a variety of other tasks. These sample jobs and reports include

  • Checking on database sizes
  • Checking on the file groups and their files
  • Checking on database sizes
  • Checking on Transaction Log autogrowth
  • Checking the number and size of Virtual Log Files (VLF)
  • Finding out when each database was last backed up (if ever!)
  • Examining table sizes
  • Creating Build scripts
  • Putting databases into source control
  • using SQL to Find all the tables in all your databases that don’t have any indexes
  • Using SQL to Find all the tables without primary keys

DRY (Don't repeat yourself)

When a database developer uses PowerShell, it is often to perform a routine operation via SMO on a number of databases on several servers or instances. This applies to a whole range of tasks. Just speaking for myself, I’ll be creating scripts for individual tables and routines, and I’d be doing entire build scripts. I’ll probably also be doing some sort of documentation, or cataloguing, of what there is in each database, or maybe the individual database settings. I’ll also be setting up a test run, or checking a status with a DMV. I might be doing checks to make sure that indexes weren’t duplicated, or that there was a primary key. I might just be searching for a string, or checking to see which users had rights of access. All these tasks require a PowerShell SMO script that accesses all or some of the databases on all or some of my instances.

After a while, the scripts will seem horribly familiar. It will probably consist of a pipeline that takes a list of servers, often the registered servers in my case, and finds all the non-system databases. It will either pick databases from a list (a whitelist), find those with a pattern in the name, or do every database that is not in a list (a blacklist). I might want to just do databases with a particular prefix or suffix. Then it will do things with each database. All that really changes is the action you want to perform with each database.

It always irritates me to have the same code in more than one place. You’ll really only want to write this once, and then reuse it. The only problem is how.

The Pleasure of PowerShell Pipelines

If you are doing a long background job serially in PowerShell, it makes sense to do this in a pipeline, rather than creating potentially huge collections of large objects, because this is kinder to the memory management of .NET. Each server object is passed down the pipeline, each to have its collection of databases passed on, one at a time to be filtered and worked on and tidied out of the way.

All you need to do is to inject the action you specify into the right part of the pipeline. To demonstrate how to do this with anonymous functions that we assign to delegates, we’ll step back slightly from the problem of accessing servers and their databases via SMO, and we’ll forget any fancy stuff like functions. We’ll demonstrate a pipeline rather differently by creating a PowerShell script to generate the lyrics for ’Ten Green Bottles’. Then, we’ll change it slightly without altering the pipeline itself in order to generate ‘There were ten in the bed’. Believe me, this will help you understand the meaty stuff later on.  This is a  relaxing way of trying out the use of scriptblocks in variables.

Ten green bottles

We’ll introduce a filter that decides the number less than, or equal to, ten at which to start the great poem. Then we’ll make the whole pipeline do something else without changing the code in the pipeline itself.

#The basic sequence
$Sequence="ten","nine","eight","seven","six","five","four","three","two","one"

$StartAt='Ten' #The number we want to start at. Try it out with other values.

$action={param ($x,$Isfirst); #the scriptblock called for every item in the list
if (-not $Isfirst) {"there would be $x green bottles hanging on the wall."}
@"

There were
$x green bottles hanging on the wall,
$x green bottles hanging on the wall
and if one green bottle should accidentally fall
"@

}


$Start={ "         $StartAt Green Bottles.`n"} #Scriptblock called at start
#and the one called after all items are processed
$Finish={ "there would be no green bottles hanging on the wall`n`n    The End`n" }
#the scriptblock that decides whether the current item should be processed
$Filter={ param ($x, $status); if ($x -eq $startAt) {$status = $true}; $status }

#the actual pipeline. We will save the scriptblock literal in a variable which we can execute
$pipeline={
$Sequence|
& { BEGIN{$ShouldOutput = $false} PROCESS{$ShouldOutput = $filter.invoke( $_, $ShouldOutput); if ($shouldOutput) {$_}} } |
    & {BEGIN{$first=$true; $Start.invoke($_) } PROCESS{$action.invoke($_, $first); $first=$false} END{$Finish.invoke($_) } }
}
$pipeline.invoke() #and we just invoke the pipeline

<# Now, we can change the poem to be ‘There were ten in the bed’ without touching the pipeline at all, but just changing the contents of the variables holding three of the four scriptblocks #>

$action={param ($x,$Isfirst); #the scriptblock called for every item in the list
if (-not $Isfirst) { "Single beds were only made for $x"}
"`nThere were $x in the bed and the little one said,"
if ($x -ne 'one') { @"
'Roll over, roll over!'.
So they all rolled over and one fell out
And he gave a little scream and he gave a little shout
'Please remember to tie a knot in your pajamas'

"@
}
else {for($ii = 1;$ii -le 3; $ii++) {"I've got the whole mattress to myself"}
"I've got the mattress to myself."}
}

$Start={ "         There were $StartAt in the Bed.`n"} #Scriptblock called at start
#and the one called after all items are processed
$Finish={ "`n`n    The End" }
#and we then just call the pipeline again!
$pipeline.invoke()

What we’re actually doing is using delegates. We are using those scriptblocks that we assign to variables as anonymous functions. We’re injecting functionality into the pipeline. We haven’t changed the code of the pipeline itself.

Creating a generic database-processing function

PowerShell can be adapted very well to the sort of programming that works by injecting functionality into a pre-existing structure. It is, after all, a dynamic language. In our case, this structure might be a pipeline that selects all your databases, one after another you and allows you to do what you need to each database.

$DataSources | # our list of servers
& {PROCESS{$ServerFilter.invoke($_)}} | # choose which servers from the list
   Foreach-object {new-object ("$My.Server") $_ } | # create an SMO server object
     Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
      Foreach-object {$_.Databases } | #for every server successfully reached
         Where-Object {$_.IsSystemObject -ne $true} | #not the system objects
            & {PROCESS{$Databasefilter.invoke($_)}} | # do all,avoid blacklist or do a whitelist etc
              & {PROCESS{$JobToDo.invoke($_)}} #and do whatever you want for the database
}

This does most routine tasks, but we need to add a few things, just as we had to with the poem. The $Serverfilter and $DatabaseFilter are variables containing a scriptblock. They have a parameter, which is $_, meaning the current object in the pipeline. This parameter isn’t strictly necessary, since it would work by using the $_ parameter within the scriptblock, but it makes things easier for debugging, or if you have a complex script. This is equivalent to a C# delegate, but without the rigorous type-checking for the parameter you pass. With the poems, the filter didn’t change. It decided how many bottles we start with, or how many children in the bed. In this case having the filter code in a variable now allows us to place the actual filter to let us choose the database when we are running the script. We might want to specify a ‘white’ list of databases that we want to operate on, or we might want to operate on all but a ‘black’ list of databases. We might want to select only those that conform to a wildcard or regex string.

In our case, we’ll just insert a whitelist or a blacklist if one has been specified. Logically, you can’t specify a whitelist if you have a blacklist: it is one or the other, or neither.

$DatabaseFilter=$TheDatabaseFilter
if ($blacklist.count -gt 0) {$DatabaseFilter= { param($x); if ($blacklist -notcontains $x.name) {$x} }}
# followed by the ones you don't want, listed in your blacklist
if ($whitelist.count -gt 0) {$DatabaseFilter= { param($x); if ($whitelist -contains $x.name) {$x} }}
# and one that just selects the files you specify in your whitelist

If you then invoke this in the process part of a scriptblock, it will filter out all the unwanted database objects as they appear, and only pass on the ones you want.

You may have already categorised your databases with special prefix such a ‘Test’. In this case, you can use a different $Databasefilter filter to get only these,

{ param($x); if ($x.name -like 'Adv*'){$x}}

Or if you want to avoid them then

{ param($x); if ($x.name -notlike 'Adv*'){$x}}

You might want to just do part of a list of databases, specifying the start and end database, just like the poems. Who knows? You can also use blacklists or whitelists for the list of servers in your care, or your registered servers, if you prefer.

Why do it this way? It is because you do not need to alter the code for the pipeline, you are ‘injecting’ your filter into it. You have your code in just one place, without duplication. With a delegate we can specify the functionality, and therefore what actually happens, at script execution-time. You can have a different filter depending on the specific parameters . To do it using conventional conditional logic would end up with a rats nest of ‘if’ statements. One can, if necessary, be even cleverer and add as many tasks as you wish to perform once the database is selected, but we won’t overdo things at this point.

So, we’ll create the pipeline as an ‘advanced’ function, so that we can provide help and allow ‘debug-printing’ and ‘voluble’ messages for debugging via Write-Debug and Write-Verbose. This means that even your anonymous functions will  be able to use these features as they are used within the same overall function. We’ll save this function in the same directory as all our code that actually does things. We'll show you the whole script at the end of the article. We've saved it in a file in the same directory. In our examples in this article the file is called ForAllDatabaseServers.ps1.

Testing out our function

So, without actually doing anything harmful to any database, lets  test it out.

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
$DataSources=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance
Foreach-DatabaseInServers $Datasources | foreach-object{"Found database '$($_.name)' on Server $($_.parent.name) "} #test that you can cannect to your datasources

Once you’ve got this working to your satisfaction, and you’ve added any extra filters or other functionality you need and tested it, then you can start doing something useful. In the following examples, I'm just checking for AdventureWorks (I've put in a filter to select only those databases starting with 'ADV', but your requirements will be different!)

Checking on database sizes

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
$servers=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance
Foreach-DatabaseInServers $Servers  `
        -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} |
   Select-object @{Name="Server"; Expression={$_.parent.name}}, Name, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage  |ConvertTo-HTML

This gives you a report that gives you the space available to each ‘Adventureworks’ database (or whatever you specify  on the servers you specify, broken down by data and index usage.

Checking on the file groups and their files

Here we drill down on detail to find out (for all our databases on all our registered servers this time)  how large each MDF file is in each group for each database. We see what group they are in, whether they are allowed to auto-grow - and if so the amount of growth, the type of growth, size of file. amount of space used and so on. This should help you to spot those MDF files where things are getting tight.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
Foreach-DatabaseInServers $Servers  |
   foreach-object{$_.FileGroups} | foreach-object{$_.files} |
     Select-object @{Name="Server"; Expression={$_.parent.parent.parent.name}}, @{Name="Database"; Expression={$_.parent.parent.name}}, @{Name="Group"; Expression={$_.parent.name}}, Name, AvailableSpace, Filename, Growth, GrowthType, size, UsedSpace, MaxSize |
        format-table

Checking on the log files

AutoGrowth

We can also check on log files on all our databases for all our registered servers (or whatever you wish, of course).  We see whether they are allowed to auto-grow, and if so the amount of growth, the type of growth, size of file. amount of space used and so on. This should alert you to such problems as long-running transactions.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
Foreach-DatabaseInServers $Servers  |
   foreach-object{$_.Logfiles} |
      Select-object @{Name="Server"; Expression={$_.parent.parent.name}}, @{Name="Database"; Expression={$_.parent.name}},  Name, Filename, Growth, GrowthType, size, UsedSpace, MaxSize |
              format-table

VLFs

We can also very simply look at the Virtual Log files for all of the databases' transaction logs, using a DBCC LogInfo directly.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
Foreach-DatabaseInServers $Servers  |
         Foreach-object {
         $Db=$_
         $_.ExecuteWithResults(‘dbcc loginfo’).Tables[0] |
           Measure-Object -Minimum -Maximum -Average FileSize |
             Select-object @{Name="Server"; Expression={$Db.parent.name}},
                           @{Name="Database"; Expression={$Db.name}}, 
                           @{Name="No.VLFs"; Expression={$_.Count}},
                           @{Name="MaxVLFSize(mb)"; Expression={"{0:n2}" -f ($_.Maximum/1MB)}},
                           @{Name="MinVLFSize(mb)"; Expression={"{0:n2}" -f ($_.Minimum/1MB)}},
                           @{Name="AverageVLF(mb)"; Expression={"{0:n2}" -f ($_.Average/1MB)}}
                           } | Format-Table * -AutoSize
 

Checking when your databases were last backed up

With a few alterations it becomes a check on when they were last backed up. After all, the function is returning the databases as SMO objects and so you can pick anything from  SMO: Database Properties or even (I’ve just shown you how!) from the SMO: Server Properties.  In this case, we'll check all our registered servers. Again,  I'm just checking for AdventureWorks (I've put in a filter to select only those databases starting with 'ADV')

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
Foreach-DatabaseInServers $Servers  `
        -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} |
   Select-object @{Name="Server"; Expression={$_.parent.name}}, Name, RecoveryModel, LastBackupDate, LastLogBackupDate, LastDifferentialBackupDate  |ConvertTo-HTML

Well, that gets a lot of standard database reports out of the way, and with  Select-Object, you can do a lot with calculated fields.  

Looking at classes within a database (Examining table size)

Here is a report that examines tables to see how many rows they have, the space they take up, the average row size and the number of columns. It is the sort of thing one would use on a quest for tables that need refactoring. It is just looking at Adventureworks in this example.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

<# here we  want to find out the data space used for each row, along with the rowcount. We then can calculate the average size of each row #>
Foreach-DatabaseInServers $servers  -whitelist @('AdventureWorks')  -jobToDo {
   param($database)
       write-verbose "Accessing the database '$($database.name)' on server $($database.parent.name)"
       $database.tables| select-object (@{Name="Server"; Expression={$database.parent.name}}, @{Name="Database"; Expression={$database.name}}, @{Name="table"; Expression={$_.name}}, @{Name="Data Space Used (Kb)"; e={$_.DataSpaceUsed}}, @{Name="Data Rows"; e={$_.RowCount}}, @{Name="no. columns"; e={$_.columns.count}}, @{Name="Average Row size (Bytes)"; e={"{0:n2}" -f (($_.DataSpaceUsed/$_.RowCount)*1024)}})
       } | format-table

Generating build scripts for a lot of our databases

How about generating a build script for all the databases of your registered servers, that is saved to a directory corresponding to the server?

In this case, we'll use $JobToDo, which is a parameter that comes in handy if you like using such things as Write-Debug and Write-Verbose or if we want to do entirely different things depending on some condition. This is because the scriptblock is now executed in the context of the function. We also need a variable to specify where to save the files.  In this case, I want a blacklist because I don’t want to script things like Adventureworks (In this example, I’ve taken out a lot of specific databases, ovbiously)

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
#and a list of the databases we don't want to be scripted
$blacklist='Pubs','NorthWind','AdventureWorks','AdventureWorksDW','ReportServer','ReportServerTempDB' #the databases I don't want to do
#where we want to store the scripts (each server/instance a separate directory
$Filepath='E:\MyScriptsDirectory' # local directory to save build-scripts to
#and do it
Foreach-DatabaseInServers -verbose $servers -blacklist $blacklist -jobToDo {param($database)
   $directory="$($FilePath)\$( $database.Parent.URN.GetAttribute('Name','Server') -replace '[\\\/\:\.]','-' )";
       $transfer = new-object ("$My.Transfer") $database
       $CreationScriptOptions = new-object ("$My.ScriptingOptions")
       $CreationScriptOptions.ExtendedProperties= $true # yes, we want these
       $CreationScriptOptions.DRIAll= $true # and all the constraints
       $CreationScriptOptions.Indexes= $true # Yup, these would be nice
       $CreationScriptOptions.Triggers= $true # This should be included when scripting a database
       $CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
       $CreationScriptOptions.IncludeHeaders = $true; # of course
       $CreationScriptOptions.ToFileOnly = $true #no need of string output as well
       $CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
       $CreationScriptOptions.Filename = "$directory\$($Database.name)_Build.sql";
       $transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
   write-verbose "scripting '$($database.name)' in server '$($database.parent.name)' to $($CreationScriptOptions.Filename)"
   if (!(Test-Path -path "$directory"))
      {
              Try { New-Item "$directory" -type directory | out-null }
           Catch [system.exception]{
                    Write-Error "error while creating '$directory' "
                return
                 }
      }
   Try {$transfer.ScriptTransfer()}
           Catch [system.exception]{
                    Write-Error "couldn't script to '$directory\$($Database.name)_Build.sql' because of error (possibly an encrypted stored procedure"
                 }
   }
'did that go well?'

Running a SQL Script on rather a lot of databases

That was pretty easy.  What about executing a SQL Script in a number of databases in a number of servers?  Right away, sir.  

Generating a single report from running the query

Let’s start with a simple example that just gives you a report of all the tables in all your databases that don’t have any  indexes. (here, we’ve just restricted it to Adventureworks). Obviously, you can supply a list of servers, maybe read from file, rather than use your registered servers. It's just handy for demonstration purposes to use registered servers.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
$SQL=@'
--Which of my tables don't have any indexes at all?
SELECT @@Servername as [Server],DB_NAME() as [Database],
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without any index]
FROM sys.tables t WHERE OBJECTPROPERTY(object_id, 'TableHasIndex')=0
order by [Tables without any index] 
'@


<#So, we can make it call some sql and get back a result. In this case we are only looking at the various AdventureWorks databases in all the servers, just to illustrate the different filters you can specify.#>
Foreach-DatabaseInServers $servers -TheDatabasefilter { param($x); if ($x.name -like Adv* ){$x}}  -jobToDo {
   param($database)
       $result=$database.ExecuteWithResults("$SQL") #execute the SQL
       $result.Tables[0]
       } | select-object ('Server','Database' ,'Tables without any index') | convertTo-html

Generating a report for each database

Now, we'll do a slightly more complicated routine. We’ll write some files, one for each database, listing in CSV all the tables in each database that are heaps, without primary keys. You’ll see that it would be dead easy to use this for a variety of reports, just by changing the SQL, (and SQL Title). I use this method for testing code on a variety of different databases, servers and collations. Note that I get the SQL Messages back, which is very important for some purposes,

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
#now fetch the list of all our registered servers
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}
$Filepath='E:\MyScriptsDirectory' # local directory to save the reports to
$SQLTitle='All_Heaps_In_'
$SQL=@'
--Which of my tables don't have primary keys?
SELECT @@Servername as [Server],DB_NAME() as [Database], --we'll do it via information_Schema
TheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'
                        +TheTables.Table_Name AS [tables without primary keys]
FROM
INFORMATION_SCHEMA.TABLES TheTables
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TheConstraints
    ON TheTables.table_Schema=TheConstraints.table_schema
       AND TheTables.table_name=TheConstraints.table_name
       AND constraint_type='PRIMARY KEY'
WHERE table_Type='BASE TABLE'
AND constraint_name IS NULL
ORDER BY [tables without primary keys]
'@


<#So, we can make it call some sql and get back a result. In this case we are only looking at the various AdventureWorks databases in all the servers, just to illustrate the different filters you can specify.#>
Foreach-DatabaseInServers $servers -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} -jobToDo {
   param($database)

      $databaseName=$database.name
       $ServerName=$database.Parent.Name
   $directory="$($FilePath)\$($database.parent.name -replace '[\\\/\:\.]','-' )";#create a directory
   #and a handler for warnings and PRINT messages
       $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
       $database.parent.ConnectionContext.add_InfoMessage($handler);
       $result=$database.ExecuteWithResults("$SQL") #execute the SQL
       $database.parent.ConnectionContext.remove_InfoMessage($handler);
       if (!(Test-Path -path "$directory")) #create the directory if necessary
             {
                     Try { New-Item "$directory" -type directory | out-null }
                  Catch [system.exception]{
                           Write-Error "error while creating '$directory' "
                       return
                        }
             }
<# you might want to save these in a central monitoring server, or put them all in one file, of course, but that is what powershell is all about #>
       $result.Tables[0]| convertto-csv -NoTypeInformation >"$directory\$SQLTitle$databasename.csv"
       }

Popping plenty of databases into source control

fine. Let’s do something slightly trickier. There is a script that generates the source, one file per object, of the databases of your registered servers. Actually, as it stands, I’ve only included those databases that start with the word ‘Test’. I suspect you’ll want something slightly different. I’ve put each server in its own directory, of which each database is in its own subdirectory. Each object type (e.g. TABLE) is in its own directory within this. From this, it is a mere bagatelle to pop these into source control, but we're not scripting that for you since I don't know what Source Control Manager (SCM) you're using.

$DirectoryToSaveTo='E:\MyScriptsDirectory' # the directory where you want to store them
$TheDatabasefilter = { param($x); if ($x.name -like 'Test*'){$x}} # just those starting with 'Phil'.

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
#get a list of the servers we want to scan
$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

$ServiceBrokerTypes=@( 'MessageType','ServiceBroker','ServiceContract','ServiceQueue','ServiceRoute','RemoteServiceBinding')

$JobToDo= {
   $database=$_
       $databaseName=$_.name
       $ServerName=$_.Parent.URN.GetAttribute('Name','Server')
       write-verbose "scripting $databasename in $serverName"
       $ScriptOptions = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")
       $ScriptOptions.ExtendedProperties= $true # yes, we want these
       $ScriptOptions.DRIAll= $true # and all the constraints
       $ScriptOptions.Indexes= $true # Yup, these would be nice
       $ScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
       $ScriptOptions.IncludeHeaders = $true; # of course
       $ScriptOptions.ToFileOnly = $true # no need of string output as well
       $ScriptOptions.IncludeIfNotExists = $true # not necessary but makes script more versatile
   $scrp=new-object ("$My.Scripter") $Database.parent
       $scrp.options=$ScriptOptions
   $database.EnumObjects([long]0x1FFFFFFF -band [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all) | `
      Where-Object {('sys','information_schema') -notcontains $_.Schema} | Foreach-Object {
   $urn=      [Microsoft.SqlServer.Management.Sdk.Sfc.Urn] $_.URN   
       if (('ExtendedStoredProcedure','ServiceBroker') -notcontains $urn.type)
          {
              $currentPath="$DirectoryToSaveTo\$($ServerName -replace '[\\\/\:\.]','-' )\$($urn.GetAttribute('Name','Database') -replace '[\\\/\:\.]','-')"
              if ( $ServiceBrokerTypes -contains $urn.type)
                           {$fullPath="$currentPath\ServiceBroker\$($urn.type)"}
          else
                           {$fullPath="$currentPath\$($urn.type)"}
      
          if (!(Test-Path -path $fullPath ))
             {
                     Try { New-Item $fullPath -type directory | out-null }
                     Catch [system.exception]{
                           Write-Error "error while creating '$fullPath' "
                          return
                         }
             }
              $scrp.options.FileName = "$fullPath\$($urn.GetAttribute('Schema')-replace '[\\\/\:\.]','-')-$($urn.GetAttribute('Name') -replace '[\\\/\:\.]','-').sql"
           $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')
           $URNCollection.add($urn)
              write-verbose "writing script to $($scrp.options.FileName)"
           $scrp.Script($URNCollection)
              }
   }
    
}

$params = @{DataSources=$Servers;TheDatabaseFilter=$TheDatabasefilter;JobToDo=$JobToDo;verbose=$true}
Foreach-DatabaseInServers @Params
"done them, Master."

 I used SMO UNCs in this routine, just to ring the changes and for the white-knuckle ride. You can use SMO objects just as well.

So here is the source of the function

 So there you have it; a simple pipeline for serving up whatever databases you specify, into which you can inject whatever functionality you need. Your requirements might be different. Your servers could be outside the windows domain and therefore accessible only by SQL Server credentials. You may need to do slightly different things to different databases. You may need to specify a process to be done at the sever level as well as the database level. In which case, hack it about and come up with something that fits your requirements. That’s the joy of a scripting language like PowerShell..

Function Foreach-DatabaseInServers {
    <#
    .SYNOPSIS
    Does the whatever scriptblock you wish for the databases in list of SQL server instances
    .DESCRIPTION
    This takes the scriptblock you define and executes it against every database that you specify, either by using a whitelist or a blacklist, or just does them all.
    .EXAMPLE
       $DataSources='Dave', 'Dee', 'Dozy', 'Beaky', 'Mick', 'Tich' # server name and instance
       $JobToDo ={param($database)
                                 $databaseName=$database.name
                                  $ServerName=$database.Parent.Name
                                  "Do stuff to $databasename on $Servername"}
       $whitelist=''#the only databases I want to do, if they're there(Leave empty otherwise)
       $blacklist='Pubs','NorthWind','AdventureWorks','AdventureWorksDW','ReportServer','ReportServerTempDB' #the databases I don't want to do

       Foreach-DatabaseInServers $Datasources $jobToDo $whitelist $blacklist
    .PARAMETER DataSources
    The list of servers that you want the files from
    .PARAMETER JobToDo
    The script of the job you want done
    .PARAMETER Whitelist
    The list of databases you want the action performed on, leaving out all others
    .PARAMETER Blacklist
    The list of databases you don't want the action performed on
    .PARAMETER TheServerFilter
    Any filter you specify for servers
   .PARAMETER TheDatabaseFilter
    Any filter you specify for databases
    .PARAMETER Initialisation
       any action that needs to be done first before the pipeline
   #>

   param([CmdletBinding()]
        # The list of databases
        [Parameter(Mandatory=$True,
                                         Position=0,
                   HelpMessage='the list of one or more SQL Server instances you would like to target')]
         $DataSources,
        # The Job To Do
        [Parameter(Mandatory=$false,
                                        Position=1,
                   HelpMessage='The actual job you want to do in each database')]
        [scriptblock] $JobToDo ={ param($x); $x},
       },
         # The WhiteList
        [Parameter(Mandatory=$false,
                                        Position=2,
                   HelpMessage='the databases in each instance that you want to select')]
         $Whitelist='',
         # The BlackList
        [Parameter(Mandatory=$false,
                                        Position=3,
                   HelpMessage='the databases in each instance that you dont want to select')]
         $Blacklist='',
        # The custom database filter you want to use
        [Parameter(Mandatory=$false,
                                        Position=4,
                   HelpMessage='if the whitelist or blacklist is no good for what you want')]
        [scriptblock] $TheServerFilter={ param($x); $x }, # define a filter that does nothing by default
        # The custom server filter you want to use
        [Parameter(Mandatory=$false,
                                        Position=5,
                   HelpMessage='if you wish to select from a list of servers')]
        [scriptblock] $TheDatabaseFilter={ param($x); $x },# define a filter that does nothing by default
        # The custom server filter you want to use
        [Parameter(Mandatory=$false,
                                        Position=6,
                   HelpMessage='any initialisation you want (with SMO loaded)')]
        [scriptblock] $Initialisation={}# define an initialisation routine that does nothing by default


     )
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null

$My="$ms.Management.Smo" #
$DatabaseFilter=$TheDatabaseFilter
if ($blacklist.count -gt 0) {$DatabaseFilter= { param($x); if ($blacklist -notcontains $x.name) {$x} }}
# followed by the ones you don't want, listed in your blacklist
if ($whitelist.count -gt 0) {$DatabaseFilter= { param($x); if ($whitelist -contains $x.name) {$x} }}
# and one that just selects the files you specify in your whitelist

$Initialisation.invoke() <# just in case you have a once-off routine you wish to execute (if you are doing SMO, since the function loads SMO.) #>

$DataSources | # our list of servers
& {PROCESS{$TheServerFilter.invoke($_)}} | # choose which servers from the list
   Foreach-object {new-object ("$My.Server") $_ } | # create an SMO server object
     Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
      Foreach-object {$_.Databases } | #for every server successfully reached
         Where-Object {$_.IsSystemObject -ne $true} | #not the system objects
            & {PROCESS{$DatabaseFilter.invoke($_)}} | # do all,avoid blacklist or do a whitelist etc
              & {PROCESS{$JobToDo.invoke($_)}} #and do whatever you want for the database
}

In case things don't work.

  • Unless you have servers called Dave, Dee, Dosy, Beaky, Mick and Tich, you'll need to change a list like ...
    $DataSources=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance
    into a list of your servers.
  • If your servers are outside your domain, then you will need to change the foreach-DatabaseInServers code suitably.
  • If you haven't got the latest SQLPS module, get it from Chad Miller's 'Making A SQLPS Module' blog
  • Make sure that you have the SMO library installed!
  • in some cases I've just selected AdventureWorks, just for the demo. You won't want that when you come to use it.
  • Use the code in the speechbubble at the top of the page
  • if you have only one item in the servers, blacklist or whitelist, still use the array syntax  e.g. @('Adventureworks')
Phil Factor

Author profile:

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 25 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Whitelist and Blacklist Problems
Posted by: Johns (not signed in)
Posted on: Friday, July 27, 2012 at 9:31 AM
Message: The whitelist and blacklist parameters did not work for me in the db scripting code.
Also is there an option to script stored procedures and if so what is it.
Thanks

Subject: Re: WhiteList and BlackList problems.
Posted by: Phil Factor (view profile)
Posted on: Friday, July 27, 2012 at 10:13 AM
Message: Ah. I remember being caught out myself during testing. If there is only one string in the array list that the Server, blacklist or whitelist parameter gets, you have to be careful to represent it as an array rather than a string. If you pass a string, Powershell tells the routine that the size of the array is zero, and it is ignored.

Have a look at an example like this from the article (snip 8< )
Foreach-DatabaseInServers $servers -whitelist @('AdventureWorks')


It is certainly easy to use Stored procedures. If the stored procedure just returns results, then those will be in the dataset. If you need to get output variables, or the return value then that takes a bit more code. Likewise if you need the print statements! Let me know if you get stuck, and I'll add an example that uses a stored procedure.

Subject: WhiteList and BlackList Works
Posted by: Johns (not signed in)
Posted on: Friday, July 27, 2012 at 12:07 PM
Message: I just ran the script again before I looked at your response and the Blacklist worked, but I didn't know why. Thanks for the explaining the reason. Previously I had only passed one database name. So, that is what that @ symbol does.

I was looking for the corresponging ScriptOption for stored procedures to the one you used for indexes below.
$CreationScriptOptions.Indexes= $true

Subject: Re: WhiteList and BlackList Works
Posted by: Phil Factor (view profile)
Posted on: Friday, July 27, 2012 at 3:14 PM
Message: Sorry, I misread the question about stored procedures.

To generate a script for stored procedures only, go to the last script for popping plenty of databases into source control and substitute the word 'StoredProcedure' for 'all' in the start of the  pipelline...
$database.EnumObjects([long]0x1FFFFFFF -band [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all)
you can use one of these instead of 'All'
ApplicationRole, AsymmetricKey, Certificate, DatabaseAuditSpecification, DatabaseEncryptionKey, DatabaseRole, Default, ExtendedStoredProcedure, FullTextCatalog, FullTextStopList, MessageType, PartitionFunction, PartitionScheme, PlanGuide, RemoteServiceBinding, Rule, Schema, SearchPropertyList, Sequence, ServiceContract, ServiceQueue, ServiceRoute, SqlAssembly, StoredProcedure, SymmetricKey, Synonym, Table, User, UserDefinedAggregate, UserDefinedDataType, UserDefinedFunction, UserDefinedTableTypes, UserDefinedType, View or XmlSchemaCollection.
You can combine them too using a bitwise OR -bor







Subject: Re: WhiteList and BlackList Works
Posted by: Phil Factor (view profile)
Posted on: Friday, July 27, 2012 at 3:26 PM
Message: Ive added the caution about the single element array to the article

Subject: Problems with Instances
Posted by: tobyharman (view profile)
Posted on: Friday, August 10, 2012 at 1:51 AM
Message: I've been trying to use this as a way to learn PowerShell, and I've come across a problem.

When I have a server with both a named and default instance of SQL, if I don't specify the TheDatabaseFilter, it only connects to the named instance.

I have 2 entries in $ServersList,
DBSERVER
DBSERVER\SQL2K5

This command (derived from the SpaceUsed) only returns the servers on named instance

Foreach-DatabaseInServers $ServersList -Blacklist $Blacklist `
| Select-object @{Name="Instance"; Expression={$_.parent.name}} , Name, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage `
| ConvertTo-HTML

Any thoughts or comments?

Subject: Problems with Instances
Posted by: tobyharman (view profile)
Posted on: Sunday, August 12, 2012 at 5:26 PM
Message: I've been trying to use this as a way to learn PowerShell, and I've come across a problem.

When I have a server with both a named and default instance of SQL, if I don't specify the TheDatabaseFilter, it only connects to the named instance.

I have 2 entries in $ServersList,
DBSERVER
DBSERVER\SQL2K5

This command (derived from the SpaceUsed) only returns the servers on named instance

Foreach-DatabaseInServers $ServersList -Blacklist $Blacklist `
| Select-object @{Name="Instance"; Expression={$_.parent.name}} , Name, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage `
| ConvertTo-HTML

Any thoughts or comments?

Subject: Problems with Instances
Posted by: tobyharman (view profile)
Posted on: Wednesday, August 22, 2012 at 1:35 AM
Message: Well I found my issue.

Turns out a predecessor of mine changed the default instance to run on a non-standard port, and put the named instance on 1433.

Changed my instance name to be <host>,<port> and away we go.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.