Parse parameter default values using PowerShell – Part 3

[ Part 1 | Part 2 | Part 3 ]

In part 1 and part 2 of this series, I introduced ParamParser: a PowerShell module that helps parse parameter information – including default values – from stored procedures and user-defined functions, because SQL Server isn't going to do it for us.

In the first few iterations of the code, I simply had a .ps1 file that allowed you to paste one or more module bodies into a hard-coded $procedure variable. There was a lot missing in those early versions, but we have addressed several things so far:

• It is now a proper module – you can run Import-Module .\ParamParser.psm1 and then call the Get-ParsedParams function throughout a session (in addition to the other benefits you get from a module). This wasn't a trivial conversion – kudos again to Will White.
• User-Defined Function support – I explained in part 2 how function names are harder to parse than procedure names; the code now handles this properly.
• Automating ScriptDom.dll – we aren't allowed to redistribute this key file, and because you can hit issues if you don't have it (or have an outdated version), Will created init.ps1, which automatically downloads and extracts the latest version (currently 150.4573.2) and places it in the same folder as the other files.
• Additional sources – you can still pass in a raw script block if you like, but now you can also use multiple instances and databases as sources, reference one or more files directly, or pull in all .sql files from one or more directories. I'll show some example syntax below.
• Output indicates source – since you can process multiple files or databases in one call, and you may have multiple objects with the same name, including the source helps disambiguate. I can't do much if you have two instances of CREATE PROCEDURE dbo.blat ... in the same file or raw script, and source is not even indicated if you use -Script and pass in a string.
• Improved output – you can still dump everything to the console, but you can also use Out-GridView to view the results in a grid format (here's a boring example from AdventureWorks2019), or log the parameter information to a database for consumption elsewhere.

Follow the instructions in the readme to download and set up. Once you've cloned the repository, run .\init.ps1 and then Import-Module .\ParamParser.psm1. Test it with a simple example, like:

Get-ParsedParams -Script "CREATE PROCEDURE dbo.a @b int = 5 out AS PRINT 1;" -GridView

Output (click to enlarge):

There are a lot of other parameter combinations too, though. The help header shows a good chunk of the possible syntax (and thanks again to Will for a lot of amazing cleanup here):

Get-ParsedParams -?

Results:

A few more examples

To parse all of the objects in c:\temp\db.sql:

Get-ParsedParams -File "C:\temp\db.sql" -GridView

To parse all of the .sql files in c:\temp\scripts\ (recursive) and h:\sql\ (also recursive):

Get-ParsedParams -Directory "C:\temp\scripts\", "H:\sql\" -GridView

To parse all of the objects in msdb on the local named instance SQL2019 using Windows authentication:

Get-ParsedParams -ServerInstance ".\SQL2019" -Database "msdb" -GridView

To parse all of the objects in msdb, floob, and AdventureWorks2019 on the local named instance SQL2019 and get prompted for SQL Authentication credentials:

Get-ParsedParams -ServerInstance ".\SQL2019" -Database "msdb","floob","AdventureWorks" -AuthenticationMode "SQL" -GridView

To parse all of the objects in msdb on the local named instance SQL2019 and pass in SQL Authentication credentials:

$password = ConvertTo-SecureString -AsPlainText -Force -String "Str0ngP@ssw0rd"
$credential = New-Object -TypeName "PSCredential" -ArgumentList "SQLAuthUsername", $password
Get-ParsedParams -ServerInstance ".\SQL2019" -Database "msdb" -AuthenticationMode "SQL" -SqlCredential $credential -GridView

To parse all of the .sql files in c:\temp\scripts\ (recursive), and put the results into a table in the local named instance SQL2019 in a database, Utility, where you've already created dbo.ParameterSetTVP, dbo.LogParameters, etc., using Windows Authentication:

Get-ParsedParams -Directory "C:\temp\scripts" -LogToDatabase -LogToDBServerInstance ".\SQL2019" -LogToDBDatabase "Utility"

To parse all of the objects in msdb on the local named instance SQL2019 and write to the Utility database on the same instance, using the same SQL Authentication credentials:

$password = ConvertTo-SecureString -AsPlainText -Force -String "Str0ngP@ssw0rd"
$credential = New-Object -TypeName "PSCredential" -ArgumentList "SQLAuthUsername", $password
Get-ParsedParams -ServerInstance ".\SQL2019" -Database "msdb" -AuthenticationMode "SQL" -SqlCredential $credential -LogToDatabase ` 
                 -LogToDBServerInstance ".\SQL2019" -LogToDBDatabase "Utility" -LogToDBAuthenticationMode "SQL" -LogToDBSqlCredential $credential

That starts to get messy, but hopefully you're automating this, and not typing it out by hand every time.

Next time

As always, there are more improvements that can be made. I don't like the parameter names I've come up with, but I think there are more important improvements, such as error handling and extensibility that should be done. Any suggestions? Please let me know or, better yet, contribute!

[ Part 1 | Part 2 | Part 3 ]