How to format SQLCMD output

Question

I am using below command line to run a SQL query using SQLCMD

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

The table has 20 columns and when i look at output command line window wraps the text and makes it difficult to read.

I want my results to be displayed the same way it displays in SQL Server Management Studio (properly formatted). I am not looking for any grids, but i need all my columns to be displayed in row 1 and the results properly beneath.

Thanks in advance.

Console windows have a fixed width. If there's more text to display in each row than the console width, what do you expect the tool to do? It cannot physically display all of the columns in row 1. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 16, 2015 at 15:13
Blegh. I occasionally need to do SQL queries and SQLCMD is horrible if you want output from it because it can only return a single string. I recommend checking out the answer here which pumps a SQL query into a table: stackoverflow.com/questions/7509591/… — Deadly-Bagel
– Deadly-Bagel, Commented Nov 16, 2015 at 15:17

Shaun Luttin · Accepted Answer · 2017-03-27 23:19:42Z

43

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.

Details

SQLCMDMAXVARTYPEWIDTH (-y)

It limits the number of characters that are returned for the large variable length data type

SQLCMDMAXFIXEDTYPEWIDTH (-Y)

Limits the number of characters that are returned for the following data types

Note: setting -y has serious performance implications.

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

answered Mar 27, 2017 at 23:19

Shaun Luttin

143k93 gold badges442 silver badges495 bronze badges

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

1 Comment

Tripp Kinetics Over a year ago

Setting -y to 0 has serious performance implications. Other values should be fine.

Klark · Accepted Answer · 2015-11-16 15:16:24Z

5

Formatting issues usually pop up due to your console window. One solution is to output to the file and use notepad/your favorite editor:

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

answered Nov 16, 2015 at 15:16

Klark

8,3183 gold badges40 silver badges63 bronze badges

2 Comments

Marcel Over a year ago

While this helps with formatting, make sure you use a proper text editor. Notepad for example still wraps long lines. I use Notepad++ and it displays almost 4000 Character long lines flawlessly.

Bluebaron Over a year ago

This is not possible if you're trying to script. I'm guessing some type of grep/sed magic is needed.

Bluebaron · Accepted Answer · 2019-09-19 16:00:09Z

2

This is how I isolated a 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'"

edited Sep 19, 2019 at 16:00

answered Sep 19, 2019 at 15:34

Bluebaron

2,5263 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to format SQLCMD output

3 Answers 3

Answer

Details

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Answer

Details

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related