Format SQL Query Output in Powershell

Question

I want to format the the output from a sql query that is run in powershell.

The script I have right now is very basic:

sqlcmd -S {Server} -d (Database) -b -Q "sp_whoisactive" | format-table

Ideally I would like to ensure that the output is in a readable format such as

Column 1        Column 2        Column 3
Data            Data2           Data3

However right now the data is all over the place and impossible to read and does not include any of the column names.

How I can accomplish this?

Possible duplicate of Formatting powershell SQL Server output into columns and rows — Ricardo C
– Ricardo C, Commented Mar 6, 2017 at 22:38
Please provide the code you are using and the result it is returning, then provide how you would want it to appear like. This will help us in assisting your needs appropriately. — Will Webb
– Will Webb, Commented Mar 7, 2017 at 10:25

ΩmegaMan · Accepted Answer · 2021-05-07 13:35:25Z

1

Powershell does not know how to handle output from sqlcmd. Instead use Invoke-Sqlcmd which is tailored to the inner workings of Powershell and can pass data as objects which can be manipulated.

If you call Invoke-Sqlcmd with a query it will list the results in a linear fashion which is not what you want. To get the format of a tabular view of the data, do again as you attempted with the sqlcmd...pipe the output into Format-Table.

Here is the command to use

Invoke-Sqlcmd -Server '{Server Name}'   '
              -Database {DatabaseName}  '
              -query "Select * from MyTable" | Format-Table

edited May 7, 2021 at 13:35

answered May 5, 2021 at 14:33

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amadeus Stevenson Over a year ago

Excellent answer thanks @ΩmegaMan - you might want backticks at the end of each multiline?

Collectives™ on Stack Overflow

Format SQL Query Output in Powershell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related