PoshCode Logo PowerShell Code Repository

Get-ProcedureCallTree (modification of post by view diff)
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1446"></script>download | new post

Uses SQLParser.ps1 script http://poshcode.org/1445 to return a Stored Procedure Call Tree

  1. #requires -version 2
  2.  
  3. #Uses SQLParser.ps1 script http://poshcode.org/1445 to return Stored Procedure Call Tree
  4. #Chad Miller
  5. #http://chadwickmiller.spaces.live.com/
  6.  
  7. param ($procedure, $server, $database, $schema='dbo')
  8.  
  9. add-type -AssemblyName Microsoft.SqlServer.Smo
  10.  
  11. #Only create the dynamic SQLParser type if it hasn't been created
  12. if (!($global:__SQLParser))
  13. {
  14.     $global:__SQLParser = ./SQLParser.ps1
  15. }
  16.  
  17. #######################
  18. function Invoke-Coalesce
  19. {
  20.     param ($expression1, $expression2)
  21.  
  22.     if ($expression1)
  23.     { $expression1 }
  24.     else
  25.     { $expression2 }
  26.  
  27. } #Invoke-Coalesce
  28.  
  29. #######################
  30. filter Get-StatementByType
  31. {
  32.     param ($statementType)
  33.  
  34.     if ($_)
  35.     { $statement = $_ }
  36.    
  37.     #If the statement of specify type is found send to output
  38.     if ($statement | Get-Member -Type Property $statementType)
  39.     { $_.$statementType }
  40.  
  41.     else
  42.     {
  43.         #If the statement type is StatementList (a collection of statements) recursively call filter Get-StatementByType
  44.         $property = $statement | Get-Member | where {$_.Definition -like "Microsoft.Data.Schema.ScriptDom.Sql.StatementList*"}
  45.         if ($property)
  46.         { $property | foreach {$statement.$($_.Name)} | foreach {$_.Statements} | Get-StatementByType $statementType }
  47.     }
  48. }
  49.  
  50. #######################
  51. function Get-ProcedureReference
  52. {
  53.     param ($procedure, $procedureText, $server, $database, $schema)
  54.  
  55.     $srv = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server
  56.  
  57.     #The sqlparser class needs the SQL version information to in order to use version specific parser
  58.     #8 is 2000, 9 is 2005 and 10 is 2008.
  59.     $sqlparser = switch ($srv.Version.Major)
  60.     {
  61.         8       { new-object SQLParser Sql80,$false,$procedureText  }
  62.         9       { new-object SQLParser Sql90,$false,$procedureText  }
  63.         10      { new-object SQLParser Sql100,$false,$procedureText }
  64.         default { new-object SQLParser Sql100,$false,$procedureText }
  65.     }
  66.  
  67.     #Fragements => Batches => Statements. The statements will be one of many different types. In this case we are looking for
  68.     #a statement type of ExecutableEntity i.e. an EXECUTE statement. Once the statement type if found output the specified properties
  69.     $sqlparser.Fragment.Batches | foreach {$_.Statements}  | Get-StatementByType 'ExecutableEntity' | foreach {$_.ProcedureReference.Name}  |
  70.     select @{n='Server';e={Invoke-Coalesce $_.ServerIdentifier.Value $server}}, `
  71.     @{n='Database';e={Invoke-Coalesce $_.DatabaseIdentifier.Value $database}}, `
  72.     @{n='Schema';e={Invoke-Coalesce $_.SchemaIdentifier.Value $schema}}, @{n='Procedure';e={$_.BaseIdentifier.Value}} |
  73.     select *, @{n='Source';e={"{0}.{1}.{2}.{3}" -f $server,$database,$schema,$procedure}}, `
  74.     @{n='Target';e={"{0}.{1}.{2}.{3}" -f $_.Server,$_.Database,$_.Schema,$_.Procedure}}
  75.  
  76. } #Get-ProcedureReference
  77.  
  78. #######################
  79. function Get-ProcedureText
  80. {
  81.     param($server, $database, $schema, $procedure)
  82.    
  83.     #Use SMO to get a reference to server, database and procedure, then call SMO script method
  84.     $srv = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server
  85.     $db= $srv.Databases[$database]
  86.     $proc = $db.StoredProcedures | where {$_.Schema -eq $schema -and $_.Name -eq $procedure}
  87.     $proc.Script()
  88.  
  89. } #Get-ProcedureText
  90.  
  91. #######################
  92. # MAIN
  93. #######################
  94. $procedureText = Get-ProcedureText $server $database $schema $procedure
  95. #SMO Script method returns a string collection, the first to elements [0] and [1] contain set statements
  96. #There is bug in SMO Script method where the statements are not terminated i.e. no ; or GO statement
  97. #Note: When script method is used with file output scripting option the statements are terminated.
  98. #In our case we don't need the SET statements, just the procedure text, which is element [2]
  99. $procedureReference = Get-ProcedureReference $procedure $procedureText[2] $server $database $schema
  100. $procedureReference
  101. #If a procedureReference object is returned recursively call the PowerShel script
  102. if ($procedureReference)
  103. { $procedureReference | foreach {./Get-ProcedureCallTree.ps1 $_.Procedure $_.Server $_.Database $_.Schema} }

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me