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
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.
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
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)
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,
Great. Thanks for sharing Matt.
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
@Mark – Good idea.. thanks for sharing.
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.
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.