{"id":100906,"date":"2024-01-05T13:07:33","date_gmt":"2024-01-05T13:07:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=100906"},"modified":"2024-01-05T16:31:22","modified_gmt":"2024-01-05T16:31:22","slug":"powershell-dynamic-parameters-and-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/powershell-dynamic-parameters-and-sql-server\/","title":{"rendered":"PowerShell Dynamic Parameters and SQL Server"},"content":{"rendered":"<p>Have you ever been in a situation that you want to call a cmdlet or a function with a parameter that depends on a conditional criteria that is available as a list? In this article I will show a technique where you can use PowerShell <a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/module\/microsoft.powershell.core\/about\/about_functions_advanced_parameters?view=powershell-7.3&quot; \\l &quot;dynamic-parameters\">Dynamic Parameters<\/a> to assist the user with parameter values.<\/p>\n<p>In the documentation of Dynamic Parameters found at <a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/module\/microsoft.powershell.core\/about\/about_functions_advanced_parameters?view=powershell-7.4\">about_Functions_Advanced_Parameters<\/a> in <code>get-help<\/code> it is defined as \u201cparameters of a cmdlet, function, or script that are available only under certain conditions.\u201d And can be created so that appears \u201conly when another parameter is used in the function command or when another parameter has a certain value.\u201d So, we can say that PowerShell Dynamic Parameters are used when the result of a parameter depends on the previous parameter.<\/p>\n<p>In the example I will present,<\/p>\n<p>I will create a function called <code>Get-SQLdatabase<\/code> that outputs database information. I will give the user the ability to use two parameters :<\/p>\n<ul>\n<li>Name of the SQL Server instance<\/li>\n<li>Optionally, choose a databases from the server specified in the first parameter.<\/li>\n<\/ul>\n<p>The functions starts by loading the assemblies and declaring the first parameter.<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">#load the assemblies\r\nimport-module sqlserver \r\n \r\nfunction Get-SQLdatabase{\r\n    [CmdletBinding()]\r\n    Param(\r\n        [Parameter(\r\n            Mandatory=$true,\r\n            Position=1,\r\n            HelpMessage=\u201dPlease provide the SQL Server name\u201d\r\n        )]\r\n        [string]$SQLserver\r\n    )\r\n <\/pre>\n<p>Parameter 1 is mandatory and corresponds to the SQL Server instance\u2019s name. This example currently only works for trusted connections, but adding a parameter for the user and password would be mostly trivial.<\/p>\n<h2>Setting up the Dynamic Parameter<\/h2>\n<p>Now we want that the second parameter to not be mandatory because you can set a specific database or all of them. If you don\u2019t include a value, then the function will show the properties of all databases on the instance.<\/p>\n<p>However, if you want to choose a single database, instead of the user needing to know all of the databases and typing one in. The following code will let you simply choose the database you want to use from a dropdown list.<\/p>\n<p>To set this up, there are a few extra configuration points we will need. We start with the parameter name, dictionary, and the collection of the attributes :<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">   # Set the dynamic parameters' name\r\n            $ParameterName = 'Database'\r\n \r\n            # Create the dictionary\r\n            $RuntimeParameterDictionary = New-Object \r\nSystem.Management.Automation.RuntimeDefinedParameterDictionary\r\n \r\n            # Create the collection of attributes\r\n            $AttributeCollection = New-Object \r\nSystem.Collections.ObjectModel.Collection[System.Attribute] <\/pre>\n<p>Second we need to create and set the parameters attributes, add the attributes to the attributes collection<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">             # Create and set the parameters attributes\r\n            $ParameterAttribute = New-Object \r\nSystem.Management.Automation.ParameterAttribute\r\n            $ParameterAttribute.Mandatory = $False\r\n            $ParameterAttribute.Position = 2\r\n \r\n            # Add the attributes to the attributes collection\r\n            $AttributeCollection.Add($ParameterAttribute)<\/pre>\n<p>&nbsp;<\/p>\n<p>Third we generate the <code>Validate<\/code> set in the <a href=\"https:\/\/learn.microsoft.com\/en-us\/dotnet\/api\/system.management.automation.validatesetattribute?view=powershellsdk-7.3.0\">System.Management.Automation.ValidateSetAttribute<\/a> class . It is in this step that we will add the database names of the SQL Server instance specified in the first parameter. Here we connect to the SQL Server instance passed in the first parameter, get the databases names and add the result in the attributes collection :<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">            # Generate and set the ValidateSet\r\n            $server = new-object\r\n (\"Microsoft.SqlServer.Management.Smo.Server\") $SQLserver\r\n            \r\n            $arrset = $server.databases.name\r\n            $ValidateSetAttribute = New-Object \r\nSystem.Management.Automation.ValidateSetAttribute($arrSet)\r\n            # Add the ValidateSet to the attributes collection\r\n            $AttributeCollection.Add($ValidateSetAttribute)<\/pre>\n<p>Finally we create and return the dynamic parameter:<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">            # Create and return the dynamic parameter\r\n            $RuntimeParameter = New-Object \r\n     System.Management.Automation.RuntimeDefinedParameter( \r\n     $ParameterName, [string], $AttributeCollection)\r\n            $RuntimeParameterDictionary.Add\r\n                         ( $ParameterName, $RuntimeParameter)\r\n            return $RuntimeParameterDictionary<\/pre>\n<p>The rest of the function is simply there to use the database name that was passed in. All it will do is show the properties of one or all databases on your server:<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">     begin {\r\n            $server = new-object \r\n      (\u201cMicrosoft.SqlServer.Management.Smo.Server\u201d) $SQLserver\r\n    }\r\n         \r\n    process {\r\n        #if the database parameter was passed shows only the \r\n        #properties of the database otherwise show \r\n        #the properties of all databases\r\n        if ($psboundparameters.Keys.Contains('database'))  {\r\n           $server.Databases[$psboundparameters.database]\r\n        } else {\r\n           $server.Databases\r\n        }\r\n    }<\/pre>\n<p>&nbsp;<\/p>\n<p>The complete function is here (or you can grab the entire code from <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/12\/LaerteJunior_dynamicparams.zip\">our website in a .zip file here<\/a>.):<\/p>\n<pre class=\"lang:ps theme:powershell-ise \">#load the assemblies\r\nimport-module sqlserver \r\n \r\nfunction Get-SQLdatabase{\r\n    [CmdletBinding()]\r\n    Param(\r\n        [Parameter(\r\n            Mandatory=$true,\r\n            Position=1,\r\n            HelpMessage=\"Please provide the SQL Server name\"\r\n        )]\r\n        [string]$SQLserver\r\n    )\r\n    DynamicParam {\r\n \r\n            # Set the dynamic parameters' name\r\n            $ParameterName = 'Database'\r\n \r\n            # Create the dictionary\r\n            $RuntimeParameterDictionary = New-Object \r\nSystem.Management.Automation.RuntimeDefinedParameterDictionary\r\n \r\n            # Create the collection of attributes\r\n            $AttributeCollection = New-Object \r\nSystem.Collections.ObjectModel.Collection[System.Attribute]\r\n \r\n            # Create and set the parameters attributes\r\n            $ParameterAttribute = New-Object \r\nSystem.Management.Automation.ParameterAttribute\r\n            $ParameterAttribut.Mandatory = $False\r\n            $ParameterAttribute.Posietion = 2\r\n \r\n            # Add the attributes to the attributes collection\r\n            $AttributeCollection.Add($ParameterAttribute)\r\n \r\n            # Generate and set the ValidateSet\r\n            $server = New-Object \r\n(\"Microsoft.SqlServer.Management.Smo.Server\") $SQLserver\r\n            \r\n            $arrset = $server.databases.name\r\n            $ValidateSetAttribute = New-Object \r\nSystem.Management.Automation.ValidateSetAttribute($arrSet)\r\n \r\n            # Add the ValidateSet to the attributes collection\r\n            $AttributeCollection.Add($ValidateSetAttribute)\r\n \r\n            # Create and return the dynamic parameter\r\n            $RuntimeParameter = New-Object \r\nSystem.Management.Automation.RuntimeDefinedParameter( \r\n$ParameterName, [string], $AttributeCollection)\r\n            $RuntimeParameterDictionary.Add( $ParameterName, \r\n$RuntimeParameter)\r\n            return $RuntimeParameterDictionary\r\n    }\r\n    begin {\r\n            $server = new-object \r\n(\u201cMicrosoft.SqlServer.Management.Smo.Server\u201d) $SQLserver\r\n    }\r\n         \r\n    process {\r\n        #if the database parameter was passed shows only the \r\n        #properties of the database\r\n otherwise show the \r\n        #properties of all databases\r\n        if ($psboundparameters.Keys.Contains('database'))  {\r\n           $server.Databases[$psboundparameters.database]\r\n        } else {\r\n           $server.Databases\r\n        }\r\n    }\r\n}\r\n#Test code to try out the function\r\n#Get-SQLdatabase -SQLserver . -Database AdventureWorks2022\r\n <\/pre>\n<h2>Testing the Dynamic Parameter<\/h2>\n<p>What this extra parameter work does for you can be seen when we start to execute the function. You can see that we have the databases listed using Intellisense when we go to get the second parameter :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1366\" height=\"768\" class=\"wp-image-100907\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/12\/word-image-100906-1.png\" \/><\/p>\n<p>As you can see in the picture all the databases from the SQL Server are listed for the second parameter.<\/p>\n<p>Obviously, the point of the code is to show how the parameters work, but If you execute the code, you will see a bunch of statistics about your database and the code. For example, for the <code>AdventureWorks2022 <\/code>database on my server, (using the PowerShell Gridview to format the output):<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">Get-SQLdatabase -SQLserver . -Database AdventureWorks2022 \r\n| Out-GridView<\/pre>\n<p>The output is something like:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1171\" height=\"478\" class=\"wp-image-100908\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/12\/a-screenshot-of-a-computer-description-automatica-12.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p>With quite a few more columns. Remove the database parameter and the output will include all of the databases on your server.<\/p>\n<h2>Summary<\/h2>\n<p>In this article we covered how the Dynamic Parameters is come in handy in particular situations we need more from PowerShell and how to use in SQL Server. One of the most typical uses is a function that you use in an Ad-hoc fashion to fetch some details from a SQL Server regularly.<\/p>\n<h3>References<\/h3>\n<p><a href=\"https:\/\/powershellmagazine.com\/2014\/05\/29\/dynamic-parameters-in-powershell\/\">PowerShell Magazine<\/a><\/p>\n<p><a href=\"https:\/\/www.foxdeploy.com\/\">FoxDeploy<\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Have you ever been in a situation that you want to call a cmdlet or a function with a parameter that depends on a conditional criteria that is available as a list? In this article I will show a technique where you can use PowerShell Dynamic Parameters to assist the user with parameter values. In&#8230;&hellip;<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,35],"tags":[],"coauthors":[6819],"class_list":["post-100906","post","type-post","status-publish","format-standard","hentry","category-featured","category-powershell"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100906","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=100906"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100906\/revisions"}],"predecessor-version":[{"id":101127,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100906\/revisions\/101127"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=100906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=100906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=100906"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=100906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}