Getting nested runbooks in system center orchestrator

I have recently written a function for checking if a runbook is checked out (will post that shortly) but I wanted a way of checking if any child runbooks were checked out too..

I did a google and found a link on ServerFault for a sql query that did just that (I've lost the link), I've taken that SQL query and wrapped it in a Powershell step so that I can use it easily on my desktop inside or outside of orchestrator - Outside of orchestrator will be to be used in my pester tests which I use to verify runbooks, but also the whole end to end process, IE are all required runbooks checked in?

The script is as follows:

function Get-ChildRunbooks {

 <#
  .SYNOPSIS
   Get-ChildRunbooks

  .DESCRIPTION
   Gets the runbooks that are called by a parent runbook, you will need permissions to query the sql database
            Requires PS V3

  .PARAMETER  RunbookName
   The runbook name to check

  .PARAMETER  Database
   The database name to connect to

  .PARAMETER  Server
   The name of the server hosting orchestrators SQL instance
 
  .PARAMETER  Recurse
   Switch for enabling recursion
            
  .EXAMPLE
   PS C:\> Get-ChildRunbooks -Name 'VMWare'

  .EXAMPLE
   PS C:\> Get-ChildRunbooks -Name 'VMWare' -Recurse
            
  .EXAMPLE
   PS C:\> Get-ChildRunbooks -Name 'VMWare' -Database Orchestrator2012

  .EXAMPLE
   PS C:\> Get-ChildRunbooks -Name 'VMWare' -Database Orchestrator2012 -Server Orchestrator001

  .LINK
   http://blog.wallis2000.co.uk

 #>
 [CmdletBinding()]
 [OutputType([System.Int32])]
 param(
  [Parameter(Position=0, Mandatory=$true)]
  [alias("Name")]
  [ValidateNotNullOrEmpty()]
  [System.String]
  $RunbookName,

  [Parameter(Position=1)]
  [System.String]
  $Database = 'Orchestrator',
        
  [Parameter(Position=2)]
  [System.String]
  $Server = 'myserver',
        
  [Switch]$Recurse
        
 )
 try {
  $Results = @()
         
$Query = @"
 SELECT POLICIES.Name AS [SourceRunbook],
   TRIGGER_POLICY.PolicyPath,
   RIGHT(TRIGGER_POLICY.PolicyPath, CHARINDEX('\', REVERSE(TRIGGER_POLICY.PolicyPath))-1) as ShortPolicyPath
 FROM OBJECTS 
  INNER JOIN POLICIES ON OBJECTS.ParentID = POLICIES.UniqueID
  INNER JOIN TRIGGER_POLICY ON OBJECTS.UniqueID = TRIGGER_POLICY.UniqueID
 WHERE (OBJECTS.Deleted = 0) AND (OBJECTS.ObjectType = '9C1BF9B4-515A-4FD2-A753-87D235D8BA1F') AND (POLICIES.Name = '$RunbookName')
"@
  
        $connectionString = "Server=$Server; Database=$Database;Trusted_Connection=Yes; Integrated Security=SSPI;"
        
        #connect to database
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        $connection.Open()

        $command = $connection.CreateCommand()
        $command.CommandText = $Query
        $command.CommandTimeout = 30

        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataset) | out-null

        foreach ($Row in $dataset.Tables[0].Rows)
        { 
            $Results+=[pscustomobject][ordered]@{
                SourceRunbook= $Row.SourceRunbook;
                PolicyPath=$Row.PolicyPath;`
                ShortPolicyPath=$Row.ShortPolicyPath}
            
            if ($Recurse) {
                $results+= Get-ChildRunbooks -Name $Row.ShortPolicyPath -Recurse
            }
        }
        $connection.Close()       
        return $results
 }
 catch {
  throw
 }
}