PowerShell Dynamic Parameters and SQL Server

Comments 0

Share to social media

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 the documentation of Dynamic Parameters found at about_Functions_Advanced_Parameters in get-help it is defined as “parameters of a cmdlet, function, or script that are available only under certain conditions.” And can be created so that appears “only when another parameter is used in the function command or when another parameter has a certain value.” So, we can say that PowerShell Dynamic Parameters are used when the result of a parameter depends on the previous parameter.

In the example I will present,

I will create a function called Get-SQLdatabase that outputs database information. I will give the user the ability to use two parameters :

  • Name of the SQL Server instance
  • Optionally, choose a databases from the server specified in the first parameter.

The functions starts by loading the assemblies and declaring the first parameter.

Parameter 1 is mandatory and corresponds to the SQL Server instance’s name. This example currently only works for trusted connections, but adding a parameter for the user and password would be mostly trivial.

Setting up the Dynamic Parameter

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’t include a value, then the function will show the properties of all databases on the instance.

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.

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 :

Second we need to create and set the parameters attributes, add the attributes to the attributes collection

 

Third we generate the Validate set in the System.Management.Automation.ValidateSetAttribute 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 :

Finally we create and return the dynamic parameter:

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:

 

The complete function is here (or you can grab the entire code from our website in a .zip file here.):

Testing the Dynamic Parameter

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 :

As you can see in the picture all the databases from the SQL Server are listed for the second parameter.

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 AdventureWorks2022 database on my server, (using the PowerShell Gridview to format the output):

The output is something like:

A screenshot of a computer

Description automatically generated

With quite a few more columns. Remove the database parameter and the output will include all of the databases on your server.

Summary

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.

References

PowerShell Magazine

FoxDeploy

 

About the author

Laerte Junior

See Profile

Laerte Junior is a Cloud and Datacenter Management MVP, focused on PowerShell and automation and, through through his technology blog and simple-talk articles, an an active member of the SQL Server and PowerShell community around the World. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and PowerShell Programming and Automation. He also is PASS Virtual Chapter Mentor , Co-Lead of the PASS PowerShell Virtual Chapter and former PASS Regional Mentor for Brasil. In addition He is also a HUGE Star Wars Fan, his main skill. "May The Force be with us" You should follow him on Twitter as @LaerteSQLDBA

Laerte Junior's contributions