Formatting requests - post them here

Andras B

We support nearly forty options to customize laying out SQL, but is it enough? We had requests for many more

So the purpose of this topic is to collect these requests. Please post your layout option requests here.

If you see a post with an option you like and would like to have this option too, please let us know via this forum, and the option request will have a higher weight when we decide on its inclusion.

Andras

almaz

In our company we are using lowercase SQL syntax. It would be nice to have an option to convert SQL statements to lowercase.

JonathanWatts

Hi there Almaz,

Yes, we were wondering how many people would want to use lowercase rather than uppercase. We will definately consider this for the next version.

Regards,

Jonathan

rdobrich

In my company we are using lowercase too.
We have a lot of XML document exchange, so it is more easy becouse XML is case sensitive.

regards,
radovan

mrshrinkray

I layout my SQL in the following way:

SELECT
	Table1.Col1,
	Table1.Col2,
	Table1.Col3
FROM
	Table1
	INNER JOIN Table2 On Table2.Col1 = Table1.Col1
WHERE
	Table1.Col1 = 123
ORDER BY
	Table1.Col1
GROUP BY
	Table1.Col2

and I also use tabs. I can't see how this is possible, it would be grande if it could be considered including using tabs.

igorolv

SQL Refactor is really great thing. Thank you.

But additionaly we use:
1) spaces before keywords

select F1,
       F2,
  from Table1
 where F1 > 0
   and F2 < 1

2) Nested joins to show join order

select F1,
       F2
  from T1
       join T2 on T2.ID = T1.T2_ID
            join T3 on T3.ID = T2.T3_ID
                 join T5 on T5.ID = T3.T5_ID
       join T4 on T4.ID = T1.T4_ID

doclane

Would like to align the commas with the columns
this:

SELECT 
    a.loanid
  , b.businessdate
  , customername = RTRIM(f.lastname) + ', ' + RTRIM(firstname)
  , c.loannumber 

To This:

SELECT 
    a.loanid
    , b.businessdate
    , customername = RTRIM(f.lastname) + ', ' + RTRIM(firstname)
    , c.loannumber

doclane

Indent line comments with next or previous line
This:

BEGIN
       SET NOCOUNT ON
--set mostrecentstat flag to no in historical table
       UPDATE
               dbo.tbHistoricalDBFileInfo
       SET
               mostrecentstat = 0
       WHERE
--move current to historical table with mostrecentstat flag set to yes
                mostrecentstat = 1

To This:

BEGIN
       SET NOCOUNT ON
       --set mostrecentstat flag to no in historical table
       UPDATE
             dbo.tbHistoricalDBFileInfo
       SET
             mostrecentstat = 0
       WHERE
              --move current to historical table with mostrecentstat flag set to yes
              mostrecentstat = 1

doclane

I noticed that in DELETE statement the from clause does not follow the rules for next line.

    DELETE FROM dbo.tbHistoricalDBFileInfo WHERE statdate < GETDATE() - 20

Becomes:

    DELETE
    FROM dbo.tbHistoricalDBFileInfo
    WHERE
        statdate < GETDATE() - 20 

where as my rules should have it become:

    DELETE
    FROM 
         dbo.tbHistoricalDBFileInfo
    WHERE
        statdate < GETDATE() - 20 

Bodhi

I would like support for the layout described by Ken Henderson in "The Guru’s Guide to Transact-SQL".

IF condition BEGIN
    SELECT . . .
END
ELSE
    ...
END

WHILE condition BEGIN
    --take actions
    . . .
END

CASE WHEN condition 
    THEN ...
    ELSE ...
END

This format is derived from the K&R book, "The C Programming Language". It makes code most readable by maximizing the "locality of the code".

Bodhi

I have my Wrap text limit set to 78 characters. When I reformat with Lay Out SQL the lines don't wrap properly. Example.

SELECT TI.Column_Name, ISNULL(LD.LocalDescription,ISNULL(D.Descriptor, Ext.[Description])) [Description]
	, TI.Alias, TI.AliasDescName
	, TI.isInBisListView, TI.IsInBisValBar
	, TI.ConstraintFilter, TI.ConstraintDescColumn
	, data_type = CASE
		WHEN C.DATA_TYPE = 'int' THEN 'integer' 
		WHEN C.DATA_TYPE LIKE '%char' THEN C.DATA_TYPE 
                                + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR) + ')'
		WHEN C.DATA_TYPE IN ('decimal', 'numeric')
		THEN C.DATA_TYPE + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR)
			+ CASE C.NUMERIC_SCALE 
				WHEN 0 THEN ''
				ELSE ','+CAST(C.numeric_scale AS VARCHAR)
				END
			+ ')'
		ELSE C.DATA_TYPE
		END
	, C.DOMAIN_NAME
	FROM information_schema.columns C		
	JOIN dbo.ft_view_sources(@table_name) AS v
		ON C.TABLE_NAME=@table_name
		AND v.view_column = C.COLUMN_NAME
-- further joins omitted here

---BECOMES---

SELECT TI.Column_Name,
                  ISNULL(LD.LocalDescription,
                         ISNULL(D.Descriptor, Ext.[Description])) [Description],
                  TI.Alias, TI.AliasDescName, TI.isInBisListView,
                  TI.IsInBisValBar, TI.ConstraintFilter,
                  TI.ConstraintDescColumn, data_type = CASE WHEN C.DATA_TYPE = 'int' THEN 'integer'
                                                            WHEN C.DATA_TYPE LIKE '%char' THEN C.DATA_TYPE + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR) + ')'
                                                            WHEN C.DATA_TYPE IN ('decimal', 'numeric') THEN C.DATA_TYPE + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR) + CASE C.NUMERIC_SCALE
                                                                                                                                                                                                                 WHEN 0 THEN ''
                                                                                                                                                                                                                 ELSE ',' + CAST(C.numeric_scale AS VARCHAR)
                                                                                                                                                                                                               END + ')'
                                                            ELSE C.DATA_TYPE
                                                       END, C.DOMAIN_NAME
           FROM   information_schema.columns C
           JOIN   dbo.ft_view_sources(@table_name) AS v
                  ON C.TABLE_NAME = @table_name
                     AND v.view_column = C.COLUMN_NAME

Because of the HTML window size limit, this might not appear quite right on your screen but I hope you see the idea.

monkeygrind

Our shop is split - 50% use lowercase for keywords, 50% use uppercase. I did notice that there is some shift on an INSERT:

I get this:

insert  into dbo.Promotion
        select  @promoID,
-- other params removed for brevity
                getdate()
        from    dbo.Promotion
        where   promoID = @oldPromoID

But what I'd expect is:

insert  into dbo.Promotion
select  @promoID,
-- other params removed for brevity
        getdate()
from    dbo.Promotion
where   promoID = @oldPromoID

Also, I'm wondering if it would be possible to hotkey between layout modes, for instance there are times when I want to use

insert into dbo.Company
     (companyid, locationid)
values
     (@companyid, @locationid)

instead of

insert into dbo.Company
(
     companyid,
     locationid
)
values
(
     @companyid,
     @locationid
)

Perhaps having setting modes, e.g. preferences 1 vs. preferneces 2. This would be ideal when different devs use slightly different standards in their DB's, or when helping someone in another group on their project where they have a different standard.

Bodhi

I would like an option to get rid of the redundant INTO in

INSERT INTO dbo.my_table (...)

Change the above to

INSERT dbo.my_table (...)

Similarly get rid of the redundant FROM in DELETE FROM

DELETE FROM dbo.my_table 
WHERE ...

Change to

DELETE dbo.my_table
WHERE ...

This will help me when I search through code for deletes or inserts on a particular table. It will also make the code nicely more compact.
I supose someone will want the reverse option to insert the redundant key words where they have been omitted.

DRBuckingham

Currently the ON portion of the join clause aligns with the parenthesis in the join hint instead of with the table name (as it does when no join hint is present). If the current formatting is desired, then please provide an option to align it with the table name when a join hint is present, so it is consistent.

Also, it currently does not pad the inside of parenthesis of functions even though the option is set in the General/Spaces section. I would like to see that option added.

I also would very much like to see a single hot key option to apply the Qualify Object Names, Expand Wildcards, Uppercase Keywords, and Lay Out SQL options in that order (or user selectable as to which ones get applied or order). It would be of extreme benefit if it were available via a batch operation to clean up all our existing (inherited) code (3000+ scripts).

I would also like to see the option to prefetch (or cache) the meta data information necessary for the Qualify Object Names so that when I am doing multiple scripts I don't have to incur the lookups each time.

Perhaps an option to replace COALESCE with ISNULL when there are only 2 arguments would be another interesting option. Granted that exceeds a reformating operation, but it is food for thought. Another option along this line would be one to alias all columns in a select statement (styled like [DateOfBirth] = Patients.DateOfBirth rather than Patients.DateOfBirth AS [DateOfBirth] or vice versa).

I would like the ability to add to the list of keywords as well. Currently "nocount" among others are not considered keywords and won't get uppercased.

Having the ability to format BEGIN/ENDs like:

IF ... BEGIN
SET ...
END

Or at minimum not indenting the BEGIN/END like:
IF ...
BEGIN
SET ...
END

Another handy feature would be to column qualify the insert statements:

INSERT INTO dbo.MyTable
VALUES ( 1, 1, 'Test' )

would become:

INSERT INTO dbo.MyTable ( Column1, Column2, Column3 )
VALUES ( 1, 1, 'Test' )

Another nicety would be able to control formating of SP calls like:

EXEC dbo.MySP @Parm1, @Parm2, @Parm3,
@Parm4, @Parm5

would optionally become:

EXEC dbo.MySP
@Parm1,
@Parm2,
@Parm3,
@Parm4,
@Parm5


Also, I agree with most of the previous suggestions as well, except for the lowercasing of the keywords. :P

vudang

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
)
SELECT DISTINCT
tl.entity_name,tl.id_1,tl.id_2,tl.id_3
FROM sometable

Andras B

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
)
SELECT DISTINCT
tl.entity_name,tl.id_1,tl.id_2,tl.id_3
FROM sometable

Hi,
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,
Andras

obbyyoyo

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:

before:

IF 1 = 1 BEGIN SELECT t.* FROM dbo.Foo WHERE t.bar = 'zee' END ELSE BEGIN SELECT t.* FROM dbo.Zee WHERE t.bar = 'foo' END

after:

IF 1 = 1 -- <-- there is a trailing space here
  BEGIN
    SELECT
      t.*
    FROM
      dbo.Foo
    WHERE
      t.bar = 'zee'
  END
ELSE  -- <-- there is a trailing space here
  BEGIN
    SELECT
      t.*
    FROM
      dbo.Zee
    WHERE
      t.bar = 'foo'
  END

Lamprey

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:

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)

BEGIN
    SELECT
        *
    FROM
        foo
END

Option 2 (With Indent)

BEGIN
        SELECT
            *
        FROM
            foo
    END

Cheers!

Lamprey

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:

RAISERROR ('Error', -1, -1)

to one with spaces after the operator:

RAISERROR ('Error', - 1, - 1)

I realize it is only whitespace, but I thought I'd mention it.

SvdSinner

I prefer to indent everything except for the initial line of a statement so that it is clear where statements begin/end. Examples:

SELECT *
    FROM table
    WHERE col1 = whatever

Of course that example is fairly trivial, but it really gets useful in cases like this:

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.

mbuis

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

SELECT ...
BEGIN TRAN
    UPDATE ...
    UPDATE ...
    IF (@@ERROR = 0)
        COMMIT
    ELSE
        ROLLBACK
SELECT ...

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.

aaronkool29

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

SELECT
@lProcedureName. = 'TestProcedure,
@lReturnCode....... = 0,
@lValue................. = 123

ad2000

Hello!

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!

Bodhi

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.

GaryJF

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!

Wyatt70

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

For example:

FROM
    [TableName] AS [TableNameAlias]
INNER JOIN [TableName1] AS T1
ON
    T1.TableID = T2.TableID
INNER JOIN [TableName2] AS T2
ON
    T2.Column = T1.Column;

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

FROM [TableName] AS [TableNameAlias]
INNER JOIN [TableName1] AS T1
ON T1.TableID = T2.TableID
INNER JOIN [TableName2] AS T2
ON T2.Column = T1.Column;

rx400h

is this possible to layout like this

exec dbo.spS
@xdoc=@xdoc
, @xmldata = @xmldata

Currently it is laying out like this


exec dbo.spS @xdoc=@xdoc, @xmldata = @xmldata

Giggles220

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)

Viking Geek

Rather than:

SELECT
    *
FROM
    HumanResources.Employee AS e 
INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE
   c.ContactID = 25
ORDER BY
    c.LastName

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

SELECT
    *
FROM
    HumanResources.Employee AS e 
INNER JOIN
    Person.Contact AS c ON e.ContactID = c.ContactID
WHERE
   c.ContactID = 25
ORDER BY
    c.LastName

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.

Lumbago

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:

    FROM
      view1 o,
      view2 a
    WHERE
      o.period = YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm,
                                                                                  GETDATE() - (DATEPART(DD, GETDATE())
                                                                                               + 1))
      AND o.period = a.period


  UPDATE
    table1
  SET
    average_balance = (
                       SELECT
                        SUM(Final_Balance) / DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))
                       FROM
                        table2 m2
                       WHERE
                        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...

WeeeBob

I love this software, but I really like my operators to have a line all to themselves, only option currently is to place operator at start or end of the line. A third option to 'Place Operator on line by itself' would be nice for me.

Place operator at start looks like this:

SELECT
    (grossSalary+2000)/100
FROM
    personnel
WHERE
    (
     empType='m'
     OR empType='n'
    )
    AND (grossSalary-@minSalary)>0

Place operator on line by itself would look like this:

SELECT
    (grossSalary+2000)/100
FROM
    personnel
WHERE
    (
     empType='m'
     OR 
     empType='n'
    )
    AND 
    (grossSalary-@minSalary)>0