Management Studio Undoing My SQL Layout in Views

Question

Management Studio Undoing My SQL Layout in Views

robert.levy

SSC Veteran

Points: 251
More actions
August 30, 2010 at 10:32 am

#239697

Dear all,
I'm sure many of you who have written queries with subqueries have taken the time to lay it out in a readable way.
For example:
select * from
(
select a, count(b)
from table1
) foo
inner join
(
select b, count(c)
from table2
) bar
on foo.b = bar.b
I find this way of laying out subqueries really helps me when it comes to making changes.
If the query is so useful, though, that I want to save it as a view, SQL Server Management Studio insists on storing the query like this:
SELECT *
FROM (SELECT a, COUNT(b)
FROM table1) AS foo INNER JOIN
(SELECT b, COUNT(c)
FROM table2) AS bar ON foo.b = bar.b
This is totally impossible to read or make changes to.
Anyone know how to stop Management Studio ruining my beautiful SQL layouts?
Thanks,
Rob
p.s. I realise the SQL in the example is nonsense. Please don't respond with "Why have you called your columns a, b and c" or "Why have you put a COUNT with no GROUP BY" 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

To make sure I understand your problem, you are defining a view and compiling it, and when you later choose "Script View As" to view its definition, you dont like the format that is generated. Is this correct?

If so, my question would be why don't you save your create view script (and perhaps even source control it) as an SQL file when creating your view. Then you can use whatever format you like.

Lowell SSC Guru Points: 323504 More actions · Answer 2

robert i'm pretty sure you have some third party object that is reformatting your SQL;

by default, SSMS is just a syntax-highlighting text editor, with no auto-reformatting as far as i know;

do you have any products installed, maybe a redgate product or something that is doing it to your code?

whether i save to a file, or compile my views, they are exactly the way i typed them in when i re-open them or script them out...so it's gotta be some "extra" feature.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Management Studio doesn't usually mess with layout unless something else is running. Do you have Red Gate SQL Prompt or one of the other layout tools?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 4

One thing I find that normally interferes with automatic formatting is the use of tabs. Try replacing tabs with spaces by selecting the code block an then going to Edit -> Advanced -> Untabify selected lines.

Also, you can set the editor to do this by default by going to Tools -> Options -> Keyboard -> all files -> tabs -> Insert spaces

If your original query did not have tabs, I am sure that the format would be preserved as-is.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

robert.levy SSC Veteran Points: 251 More actions · Answer 5

Hi Nakul, Grant, Lowell and Nevyn,

I'm really sorry not to have checked this further before posting. It seems I misunderstood where the reformatting was occuring.

It's only when I right-click the View and use the "Design" option that the formatting gets messed up.

When I posted yesterday, I had viewed the View in the Editor mode then saved it, not realising what had happened to the layout of the SQL.

You are all correct: If I just do CREATE VIEW foo AS... then then view is stored as typed.

I still fail to understand why the graphical view/query editor makes the SQL look so ridiculous, but that's another problem, possibly for another post! (I hate it when people 'repurpose' a post halfway through!)

Many thanks for your responses, which encouraged me to check this again!

Best,

Rob

Autumn_Ranjan SSC Rookie Points: 47 More actions · Answer 6

If you do find yourself in a situation where the code is still indented more than necessary, there is a somewhat-decent shortcut...If you select the code and hold down the Shift-Tab keys, it will decrease the indentation of the code and line it up into a readable format.

bwperrin Old Hand Points: 333 More actions · Answer 7

Robert, did you ever get a satisfactory answer to this? I'm getting a new behavior lately where "AS" is moved up to the view declaration line, and my view's first line's leading white space (a tab) is removed. Used to work; now it doesn't (SSMS 11.0.5343.0). The remaining tabs are preserved (and removing tabs is not an option - I would die).

Anyone?