How to format SQLCMD output

Question

I use the command line below to run an SQL query using SQLCMD

sqlcmd -S Server -Q "select top 100 * From people" -d people -t 10 

The table consists of 20 columns, and when I look at the output command line window, it wraps the text and makes it difficult to read.

I want my results to display the same way they appear in SQL Server Management Studio (formatted correctly). I'm not looking for grids, but I need all my columns to appear in row 1, and the results to be properly displayed below.

Thank you in advance.

Answer 1

Answer

We can set the width of each column.

 C:/> sqlcmd -S my_server > :setvar SQLCMDMAXVARTYPEWIDTH 30 > :setvar SQLCMDMAXFIXEDTYPEWIDTH 30 > SELECT * from my_table > go 

We can also set it like this: sqlcmd -S my_server -y 30 -Y 30 .

More details

SQLCMDMAXVARTYPEWIDTH ( -y )

It limits the number of characters returned for a large variable length data type.

SQLCMDMAXFIXEDTYPEWIDTH ( -Y )

Limits the number of characters returned for the following data types

Note: tuning -y has serious performance implications.

See https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

Answer 2

Formatting problems usually appear due to the console window. One solution is to output to a file and use the notepad / of your favorite editor:

 sqlcmd -S myServer -d myDB -E -Q "select top 100 * From people" -o "output.txt" 

Answer 3

This is how I isolated the scalar.

 sqlcmd -S xxx.xxx.xxx.xxx,xxxxx -d MyDb -U myUser -P MyPassword -h -1 -W -Q "set NOCOUNT ON; select a from b where b.id='c'"