Using Out-GridView To Pick Parameters–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article on PoSh and learned something cool. I can use Grid-View to surface an object in a pipeline and then change the items passed through to the next command.

Here’s how it works. Suppose I have a simple comment, like Get-SqlDatabase. I can run this and get a series of database. (command and output shown here).

Get-SqlDatabase –ServerInstance .\SQL2014

2017-01-03 12_50_20-powershell

I get a bunch of data, but I don’t want all the items. For example, maybe I just want the “SimpleTalk” databases.

Get-SqlDatabase -ServerInstance .\SQL2014 | Where-Object { $_.Name -like ‘SimpleTalk*’}

2017-01-03 12_51_42-powershell

I don’t want to edit this Where-Object clause all the time. I want something simple to easily fix this.

Enter Out-GridView

Instead of changing my filter, I can do this graphically. I can pipe my output to Out-Gridview, and I’ll get this.

2017-01-03 12_54_44-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

All of my output data appears. No big deal, right? Is this useful? It sure is. Look at the filter item above. I can type in there, and my data is filtered.

2017-01-03 12_55_29-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

How can I use this? Suppose my code is this:

PS SQLSERVER:\SQL\Plato\SQL2016\Databases> Get-SqlDatabase -ServerInstance .\SQL2014 | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption Default –Script

Here I’m getting a list of databases, passing them to Out-GridView, and then sending the results to Backup-SqlDatabase, which will return a script to back up all my databases.

I’ll run this, and then enter a filter in the grid.

2017-01-03 12_57_47-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

I can now highlight all these rows (if that’s what I want).

2017-01-03 12_59_11-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

This time I have an “OK” button in the lower right corner of the grid.

2017-01-03 12_58_23-Movies & TV

When I click “OK”, my filtered list is returned to the pipeline and send to the backup command.

2017-01-03 12_59_32-powershell

This is a quick way to work with the parameters in your pipeline in an ad hoc way. Use Out-GridView to filter and select the rows you want to return to the rest of your script.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.