How to format SQLCMD output

sql sql-server powershell sqlcmd
user1550159 picture user1550159 · Nov 16, 2015 · Viewed 18.9k times · Source

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.

Answer

Shaun Luttin picture Shaun Luttin · Mar 28, 2017

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://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

Related questions

How to execute .sql file using powershell?

I have a .sql file. I am trying to pass connection string details through a Powershell script and invoke an .sql file. I was searching and came up with a cmdlet related to Invoke-sqlcmd. While I was trying to find …

Read More
Error restoring database backup to new database with smo and powershell

Taking a database backup from another server I'm trying to restore to sqlexpress on the localhost. This restore will work via the gui but I'm having issues restoring it with powershell. I get the following error message: Exception calling "SqlRestore" …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More