CODEWITHSUNDEEP
sqlsql-serverpowershellsqlcmd
user1550159
user1550159

Reputation: 1267

How to format SQLCMD output

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.

Upvotes: 23

Views: 38234

Answers (3)

Bluebaron
Bluebaron

Reputation: 2516

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'"

Upvotes: 2

Shaun Luttin
Shaun Luttin

Reputation: 141512

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

Upvotes: 41

Klark
Klark

Reputation: 8280

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"

Upvotes: 5

Related Questions