PowerShell – Tip: How to Format PowerShell Script Output?

SQL
5 Comments

I have been writing on various ways of working with PowerShell and how to connect to SQL Server. Personally, when I see text on command prompt it is quite a mess and very difficult to decipher the same. If you play around and look at various blog posts, they show some interesting outputs even though they work to format PowerShell.

In this blog post, let me take you through 3 of the most common ways in which people format works with PowerShell script. This is not always the exhaustive way of working, but a great start and you will surely start falling in love with this capability. Trust me on it.

Method 1: This is an age old classic wherein we can format the output from a PowerShell script in a formatted table (ft) to be short. A typical command looks like:

#Demo Format-Table cmdlet. Alias ft
invoke-sqlcmd "Select * from sys.dm_exec_connections" -ServerInstance . | ft

PowerShell - Tip: How to Format PowerShell Script Output? format-powershell-01

Solarwinds

As you can see above, the output of our SQL query is not formatted in a nice table structure and is properly delimited. I am generally a big fan of using this with my scripts as it is easily readable.

Method 2: This is yet another variation of the same output, but this time we can take the output and make it into a formatted list (fl). A typical usage of this would look like:

#Demo Format-List cmdlet. Alias fl
invoke-sqlcmd "Select * from sys.dm_exec_connections" -ServerInstance . | fl

There are people who like to see these as property sheets and I am not inclined to this output in general. But I am sure there will be use cases wherein it would make complete sense to have this output.

PowerShell - Tip: How to Format PowerShell Script Output? format-powershell-02

Option 3: This is a revelation for me. I was using PowerShell ISE and one of the output is to use the GridView. This can be used like:

#Demo Format-List cmdlet. Alias Gridview
invoke-sqlcmd "Select * from sys.dm_exec_connections" -ServerInstance . | Out-Gridview

PowerShell - Tip: How to Format PowerShell Script Output? format-powershell-03

As you can see, the output now is in the format of a window just like what I am used to with SQL Server Management Studio. This was an awesome capability I personally felt.

I am sure many of you are power users and might have used these in different ways. Please let me know which of the output you like the most and let me know if there are any other methods that I need to know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
PowerShell Scripts – get-process with SQL Server process
Next Post
SQL SERVER – Displaying SQL Agent Jobs Running at a Specific Time

Related Posts

5 Comments. Leave new

  • Hi,

    Everybody *probably* knows this already, but just in case, ‘ft -Autosize’ (or ‘ft -a’ for short) sometimes gives better results than ‘ft’ on its own,

    Best,

    Matt

    Reply
  • Sometimes ft likes to truncate longer data. One way to get around this is to use it in conjunction with out-string. Example: ft -a | out-string -width 4096

    Reply
  • Hi.
    Very nice article. I just have one question.
    It’s possible view how much rows are changed (like in MSSQL Studio) when you perform some updates via Invoke-Sqlcmd.

    For example:
    Invoke-Sqlcmd -InputFile “C:\temp\updatescript.sql” -ServerInstance Server01 -Database DB01

    I need to execute script remotely and to be sure that rows are changed.
    Thank you in advance.

    Reply

Leave a Reply

Menu