Format Code using Layout SQL in SQL Prompt 3.8 Pro

klorimer

This is a minor formatting question but when you format the following;

SELECT field
FROM table
ORDER BY field

it lines everything up based on the SELECT rather than the order by which results in the order by fields being one character to the left of everything else.

Can it be configured to ensure all items after the key words line up in a column?

tanya

Hi,

Just to ensure I understood it right, would you like to see all the fields aligned like this:
SELECT <columnname>
FROM <tablename>
ORDER BY <columnname>

If so , you can do this by going to SQL Prompt options - Layout - Tab size and wrapping - And set the 'number of spaces equivalent to a tab stop' to 3 or a value that suits you best.

Does that help?

Thanks,
Tanya

klorimer

Thanks, that worked perfectly!

iceburg

When formatting my code, it ends up with spaces around delimiter dots, for example: Select * from dbo . MyTable instead of dbo.MyTable. I didn't even know that you could put spaces around dots like that, but I don’t particularly like it. Is this a bug or a feature?

tanya

Hello,

That is really bizarre!
There is no option in layout to insert spaces before or after dots and I am not able to reproduce this issue in our test environments.
Does it happen all the time or is it unpredictable?
Also, can you tell us if the issue happens only on particular editors? And do you have SQL Refactor installed in your machine?

Thanks,
Tanya

iceburg

It happens when a select statement is enclosed in parenthesis such as a derived table or a subquery:

select
SubQueryColumn = (
select count (*) from dbo . MyTable
)

select
MyCount
from
(
select count (*) as MyCount from dbo . AnyTable
) as MyDerivedTable

iceburg

tanya wrote:

...Also, can you tell us if the issue happens only on particular editors? And do you have SQL Refactor installed in your machine?...

This was on SSMS '05. I don't have SQL Refactor.

iceburg

Tanya, Were you able to reproduce this?

tanya

I have tried using different configurations on the layout options menu and am still not successful with reproducing the issue!

Is there any possibility that you can try this on a different machine or a different editor so that we can check if this is related to some system or editor configuration at all? I am not really sure why that should happen but it would help if we can confirm that.

Thanks,
Tanya