CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005ssms
Jamie Stuart Robin Parsons
Jamie Stuart Robin Parsons

Reputation: 1152

Resultset column width in Management Studio

Is there any way to alter the column width of a resultset in SQL Server 2005 Management Studio?

I have a column which contains a sentence, which gets cut off although there is screen space. 

| foo | foo2 | description |          | foo | foo2 | description         |
|--------------------------|    TO    |----------------------------------|
|  x  |  yz  | An Exampl.. |          |  x  |  yz  | An Example sentence |

I would like to be able to set the column size via code so this change migrates to other SSMS instances with the code.

Upvotes: 31

Views: 25996

Answers (7)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

No, the width of each column is determined at runtime, and there is no way to override this in any version of Management Studio I've ever used. In fact I think the algorithm got worse in SQL Server 2008, and has been essentially the same ever since - you can run the same resultset twice, and the grid is inconsistent in the same output (this is SQL Server 2014 CTP2):

enter image description here

I reported this bug in 2008, and it was promptly closed as "Won't Fix":

  • SSMS : Grid alignment, column width seems arbitrary (sorry, no link)

If you want control over this, you will either have to create an add-in for Management Studio that can manhandle the results grid, or you'll have to write your own query tool.

Update 2016-01-12: This grid misalignment issue should have been fixed in some build of Management Studio (well, the UserVoice item had been updated, but they admit it might still be imperfect, and I'm not seeing any evidence of a fix).

Update 2021-10-13: I updated this item in 2016 when Microsoft unplugged Connect and migrated some of the content to UserVoice. Now they have unplugged UserVoice as well, so I apologize the links above had to be removed, but this issue hasn't been fixed in the meantime anyway (just verified in SSMS 18.10).

Upvotes: 21

Czarnecki93
Czarnecki93

Reputation: 1

It is actually possible.

In SQL Server Management Studio press Tools > Options > Expand the menu "Query Results" on the left > choose "SQL Server" > In the dropdown choose "Results to text" instead of "Results to grids" > Press OK and it should work.

It is described here with pictures as well https://social.msdn.microsoft.com/Forums/en-US/9b63d53f-211c-4174-917c-31d9567f2132/ssms-1831-column-width-is-not-auto-sizing?forum=sqlkjmanageability

Upvotes: 0

Morten H
Morten H

Reputation: 31

Resultset column width is adjusted to header text:
SELECT [FieldName] as [Header Name followed by whitespace               ]
FROM ...

Upvotes: 3

Alex Ortiz
Alex Ortiz

Reputation: 39

I also had this issue. My solution was to change the font that was displayed in the results grid from the default sans-serif non-monospace font to one that was monospace.

One may change the font used on the results grid this way:

  1. Navigate to the Tools -> Options... menu.
  2. Within the environment cascading menu, select "Fonts and Colors". A corresponding menu is displayed to the right.
  3. In the "Show Settings For" dropdown, select "Grid Results".
  4. Select a new font and font size that is monospace. I used consolas.
  5. Save your queries and exit Microsoft SQL Management Studio.
  6. Execute a query that outputs to the grid. Monospace fonts will have column widths generated properly.

Upvotes: 3

Krahul3
Krahul3

Reputation: 37

If you are only concerned about increasing the column width on the screen to view complete text within that column, you can do the following: -

1.) In the result set

2.) take your mouse over the column heading of the column you want to widen

3.) Using your left mouse button simply hold the line that divides two columns ( right one for the column you want to expand), and you will see your mouse cursor will change to adjustment mode (vertical black line with arrows moving out towards left and right). drag it towards the the right or left to increase or decrease the width respectively.

Just like we do it in excel, hold and drag.

Upvotes: -2

Anonymous Coward
Anonymous Coward

Reputation: 626

What you can do is alias the selected field like this:

SELECT name as [name        .] FROM ...

The spaces and the dot will expand the column width.

Upvotes: 47

Sean
Sean

Reputation: 133

How are you outputting - to text or to grid?

If to text, try this - In SSMS, go to Tools, then Options...

In Options, go to Query Results, then SQL Server. Select Results to Text and look at the Maximum Number of Characters Displayed in Each Column

See if that's set low, and if expanding it helps. You'll need to start a new query window when you make your change. Existing windows don't seem to pick up the changes.

Upvotes: 3

Related Questions