Get stored proc names and parameters from the database with PowerShell

Get-StoredProcParameters.ps1

<#
.SYNOPSIS
Get JSON out put of SQL Stored Proc names and their parameters
#>
[CmdletBinding()]
param(
[Parameter(Mandatory)]
[string] $serverInstance = '.',
[Parameter(Mandatory)]
[string] $database = 'test',
[PSCredential] $Credential,
[string] $outFile = "c:\temp\StoredProcs.json"
)

$ErrorActionPreference = "Stop"

$progressActivity = "Getting procs"
Write-Progress -Activity $progressActivity -CurrentOperation "Getting list..."

$credParam = @{}
if ($Credential)
{
    $credParam["Credential"] = $Credential
}

$fullQuery = @"
SELECT [SPECIFIC_SCHEMA]+'.'+[SPECIFIC_NAME] as PROC_NAME
,[ORDINAL_POSITION]
,[PARAMETER_MODE]
,[IS_RESULT]
,[AS_LOCATOR]
,[PARAMETER_NAME]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[CHARACTER_OCTET_LENGTH]
,[COLLATION_CATALOG]
,[COLLATION_SCHEMA]
,[COLLATION_NAME]
,[CHARACTER_SET_CATALOG]
,[CHARACTER_SET_SCHEMA]
,[CHARACTER_SET_NAME]
,[NUMERIC_PRECISION]
,[NUMERIC_PRECISION_RADIX]
,[NUMERIC_SCALE]
,[DATETIME_PRECISION]
,[INTERVAL_TYPE]
,[INTERVAL_PRECISION]
,[USER_DEFINED_TYPE_CATALOG]
,[USER_DEFINED_TYPE_SCHEMA]
,[USER_DEFINED_TYPE_NAME]
,[SCOPE_CATALOG]
,[SCOPE_SCHEMA]
,[SCOPE_NAME]
FROM [INFORMATION_SCHEMA].[PARAMETERS]
"@

$query = @"
SELECT [SPECIFIC_SCHEMA]+'.'+[SPECIFIC_NAME] as [SchemaDotName]
      ,Case [PARAMETER_MODE] when 'IN' THEN 'Input' when 'OUT' then 'Output' when 'INOUT' then 'InputOutput' END Direction
      ,CASE [IS_RESULT] WHEN 'YES' THEN 'true' ELSE 'false' END IsResult
      ,[PARAMETER_NAME] ParameterName
      ,[DATA_TYPE] SqlDbType
      ,COALESCE([CHARACTER_MAXIMUM_LENGTH],0) as Size
  FROM [test].[INFORMATION_SCHEMA].[PARAMETERS]
"@

$result = Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database @credParam -Query $query

$output = @()
foreach ($g in $result | Group-Object SchemaDotName)
{
    Write-Progress -Activity $progressActivity -CurrentOperation $r.Name -PercentComplete ([int](100*$i/$result.Count))
    $parts = $g.Name -split '\.'
    $proc = @{
        Schema=$parts[0]
        Name=$parts[1]
        Parameters=@($g.Group | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors, SchemaDotName)
    }
    $output += $proc
}
$output | ConvertTo-Json -Depth 4 | Out-file $outFile -Encoding ascii