Formatting requests - post them here

Refactors and formats SQL code.


Postby Andras » Mon Dec 11, 2006 12:02 pm

vudang wrote:I'm not sure if this is available as an option but I cannot get sql refactor to format the insert column list on one line when using insert select. Basically the following is not possible.

INSERT INTO #extract_tlot
entity_name, id_1,id_2,id_3
FROM sometable

this one is actually a bug, and this will be fixed in version 1.1. I will also contact you concerning this in a private message. Many thanks,
András Belokosztolszki, PhD
Red Gate Software Ltd.
Posts: 249
Joined: Thu May 19, 2005 4:07 pm
Location: Cambridge, UK

Trailing Spaces

Postby obbyyoyo » Wed Feb 14, 2007 6:47 pm

This is a nice-to-have kind of thing that would comfort the compulsive side of me.

I would like to always have trailing spaces removed, or at least have it as an option.

simplistic example:

Code: Select all
IF 1 = 1 BEGIN SELECT t.* FROM dbo.Foo WHERE = 'zee' END ELSE BEGIN SELECT t.* FROM dbo.Zee WHERE = 'foo' END

Code: Select all
IF 1 = 1 -- <-- there is a trailing space here
    WHERE = 'zee'
ELSE  -- <-- there is a trailing space here
    WHERE = 'foo'
Posts: 2
Joined: Wed Feb 14, 2007 6:38 pm


Postby Lamprey » Thu Feb 22, 2007 8:46 pm

I know there are many ways to indent/format blocks of SQL. Specifically, the BEGIN-END blocks. I noticed someone liked the BEGIN at the end of the previous statement like:

Code: Select all
IF (x > 1) BEGIN

That aside, I would like to see an option whether or not to indent (Assuming I did not miss this feature). So, the option would work like this:

Option 1 (No Indent)
Code: Select all

Option 2 (With Indent)
Code: Select all

Posts: 3
Joined: Thu Feb 22, 2007 8:37 pm

Expression issue

Postby Lamprey » Thu Feb 22, 2007 9:05 pm

One other thing I noticed is a potential problem formatting expressions that contain operators. I have my option set to place a space around operators so that things are spaced out a bit. Because of this setting it changes a statement like:

Code: Select all
RAISERROR ('Error', -1, -1)

to one with spaces after the operator:

Code: Select all
RAISERROR ('Error', - 1, - 1)

I realize it is only whitespace, but I thought I'd mention it.
Posts: 3
Joined: Thu Feb 22, 2007 8:37 pm

Postby SvdSinner » Tue Feb 27, 2007 3:26 pm

I prefer to indent everything except for the initial line of a statement so that it is clear where statements begin/end. Examples:
Code: Select all
    FROM table
    WHERE col1 = whatever

Of course that example is fairly trivial, but it really gets useful in cases like this:
Code: Select all
INSERT table
    (col1, col2, col3)
    SELECT column1, column2, column3
        FROM table2
        WHERE condition = true

Much easier to read, IMHO. The standard form (aligning all clauses) leaves you wondering if SELECT statements are standalone or part of a larger query. This makes is simpler to determine at a glance what belongs to what.
Posts: 11
Joined: Tue Feb 27, 2007 3:00 pm
Location: North of my cattle barn, Ames, IA

Indent body of transaction

Postby mbuis » Thu Apr 12, 2007 1:23 pm

I would like the option to indent the body of a transaction:

Code: Select all
    UPDATE ...
    UPDATE ...
    IF (@@ERROR = 0)

BEGIN TRAN would increase the indent level. Knowing when to decreasing it would be a but tricky to determine, as there may be conditional logic involved.
Posts: 3
Joined: Mon Nov 20, 2006 6:36 pm

Location of Variable Type Definitions & Select Statement

Postby aaronkool29 » Thu Apr 12, 2007 1:30 pm

Changed to the Following *(Note the Periods are just for Formatting on this post, those would appear as spaces in SQL)
@lProcedureName .VARCHAR(100),
@lReturnCode .......INTEGER
@lValue .................INTEGER

@lProcedureName. = 'TestProcedure,
@lReturnCode....... = 0,
@lValue................. = 123
Posts: 2
Joined: Mon Mar 12, 2007 5:25 pm

Case sensitivity and squared brackets

Postby ad2000 » Fri Apr 13, 2007 9:30 am


I am testing SQL Refactor and I suggest two more features:

1. I would prefer that the object names and column names would be formatted with case sensitivity.

2. And I would like to have the squared brackets set automatically to object and column names like SELECT [EmployeeID], [Title], [NationalIDNumber] FROM [HumanResources].[Employee];

Thank you!
Posts: 1
Joined: Fri Apr 13, 2007 9:08 am

Formatting object names with case sensitivity

Postby Bodhi » Fri Apr 13, 2007 3:21 pm

The run time performance is better when the object names are qualified by the schema name AND the object name case is correct. So, formatting with case correction would be a valuable feature.
Posts: 6
Joined: Wed Nov 22, 2006 6:01 pm
Location: Spokane, Washington

Layout as per Microsoft Help Files

Postby GaryJF » Sun May 20, 2007 1:38 am

As a consultant, I have been to many different companies and I have noticed that quite a few of them use the defacto Microsoft layout, i.e. the layout you find in the help files.

You cannot achieve this layout with the current version of Refactor, which is a shame. Although, this is unsurprising considering the many layouts being utilised in the industry, I can see a much wider audience making use of it if it was able to layout the code in the same format as the help files.

Probably the most imortant changes to assist in this would be:
1. The ability to align comments with the next line of code
2. The ability to have BEGIN END blocks align directly under the keyword it serves
3. The ability to list colums horizontally in SELECT statements, yet vertically in modification statements (UPDATE and INSERT)
4. The ability to decide which keyword types should be uppercased (for example, uppercase all except datatypes)

There are a few others, which can easily be spotted by copying SQL Server example code and laying it out to see the changes.

If Refactor ever becomes as flexible as SQL Prompt, then it would be an invaluable asset in laying out the code. As for now, I use refactor solely for its other features, like Smart Rename, Script Summary etc. I have just purchased both the SQL Compare Bundle and the SQL Prompt Bundle, so I am a big fan of Red-Gate tools...but I know I could encourage many more of my clients to invest in the SQL Prompt Bundle when the layout feature matches the flexibility of Prompt!
The client doesn't know what he wants, until he doesn't get it!
Posts: 5
Joined: Thu May 17, 2007 10:03 am
Location: N. Ireland

Hanging Indent

Postby Wyatt70 » Thu May 24, 2007 5:43 pm

Why do the statements following FROM, JOIN, ON use a "hanging indent"?

For example:

Code: Select all
    [TableName] AS [TableNameAlias]
INNER JOIN [TableName1] AS T1
    T1.TableID = T2.TableID
INNER JOIN [TableName2] AS T2
    T2.Column = T1.Column;

I would like to be able to display it this way:

Code: Select all
FROM [TableName] AS [TableNameAlias]
INNER JOIN [TableName1] AS T1
ON T1.TableID = T2.TableID
INNER JOIN [TableName2] AS T2
ON T2.Column = T1.Column;
Posts: 36
Joined: Fri Jul 29, 2005 5:17 pm
Location: Yonkers, NY

exec sp layout

Postby rx400h » Thu May 24, 2007 8:28 pm

is this possible to layout like this

exec dbo.spS
, @xmldata = @xmldata

Currently it is laying out like this

exec dbo.spS @xdoc=@xdoc, @xmldata = @xmldata
Posts: 2
Joined: Thu May 24, 2007 8:13 pm

Postby Giggles220 » Fri May 25, 2007 4:30 pm

Out of the listed suggestions I'd like to see the following:

- Spaces before keywords
- Nested Joins to show join order
- align commas with columns
- indent line comments with next or previous line
- remove trailing spaces
- object name and column name formated with case sensitivity

- Ability to select upper or lower case for certain objects (for example I like my commands to be in upper case but my objects maybe to be lower case)
Posts: 114
Joined: Thu Dec 07, 2006 8:06 pm
Location: Albany, NY

JOIN layout and tabs vs spaces

Postby Viking Geek » Sat May 26, 2007 6:11 am

Rather than:

Code: Select all
    HumanResources.Employee AS e
INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID
   c.ContactID = 25

I would prefer to have the joined table and condition on a new line, like this:

Code: Select all
    HumanResources.Employee AS e
    Person.Contact AS c ON e.ContactID = c.ContactID
   c.ContactID = 25

I think that is more consistent with the layout, as major keywords are alone on each line.

I would also prefer to use tabs over spaces for indents.
Viking Geek
Posts: 1
Joined: Sat May 26, 2007 6:04 am

Postby Lumbago » Tue Jun 12, 2007 8:24 am

I would *very much* appreciate the possibility to force an indentation to be what you have set it to be. I have set my indentation setting to insert tabs as spaces and one tab = 2 spaces and to wrap text at 160 characters. However lay out sql gives me these to statements which to me makes no sense:
Code: Select all
      view1 o,
      view2 a
      o.period = YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm,
                                                                                  GETDATE() - (DATEPART(DD, GETDATE())
                                                                                               + 1))
      AND o.period = a.period

    average_balance = (
                        SUM(Final_Balance) / DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))
                        table2 m2
                        period BETWEEN YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + 1
                               AND     YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))

Both of these are from the same procedure and are laid out at the same time. In the fiste statement the o.period = YEAR(... is wrapped two times and indented like crazy but the last where-statement in the sub-query isn't wrapped at all. I'd also think that the indentation setting would place the last sub-select one tab (2 spaces in my case) from "average_balance" while it is actually aligned with the opening parenthesis. The indentation in the subselect is also only one character...
Posts: 25
Joined: Mon May 22, 2006 8:03 am


Return to SQL Refactor Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest