Formatting requests - post them here

Wyatt70

I think you would be better off if you had some kind of configuration file that we could edit to suit our purposes. I'd rather just customize my template the way I see fit than make all these posts to a forum and hope that enough people agree with me.

gfrizzle

I would like to see FETCH statements handled better. Right now, they appear on a single line:

FETCH NEXT FROM myCursor INTO @myVar1, @myVar2, @myVar3

But I would expect the option to lay it out something like this:

FETCH NEXT
   FROM myCursor
   INTO
      @myVar1,
      @myVar2,
      @myVar3

Baldy

almaz wrote:

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

I definitely need the lowercase option as well

Baldy

We have a standard to indent keywords of a statement as in

select ...
  from...
  where
    and
     or

It just makes for more readable code.

But since everyone has their own standards, it would be nice to set the indentation rules per keyword

Henrik Staun Poulsen

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

I would like to second that.

bogieman

1. the ability to select tabs instead of spaces for indentation
2. case statement handle the when/then like a begin

my code:

,	case
	when dbo.dfh_v_Invoice_Memo.rectype = 'detail' then
		case when dbo.dfh_v_Invoice_Memo.source = 'erp' then
			case when dbo.dfh_v_Invoice_Memo.doctype = 'invoice' then
				case when isnull(dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom, '') = '' then 
					dbo.so_prod_tbl.en_uom_pricedef
				else
					dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom
				end
			else
				case
				when isnull(dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom, '') = '' then
					dbo.so_prod_tbl.en_uom_pricedef
				else
					dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom
				end
			end
		when dbo.dfh_v_Invoice_Memo.source = 'iave' then
			dbo.so_prod_tbl.en_uom_pricedef
		else
			''
		end
	else
		''
	end

Refactor:

,       case when dbo.dfh_v_Invoice_Memo.rectype = 'detail' then case when dbo.dfh_v_Invoice_Memo.source = 'erp' then case when dbo.dfh_v_Invoice_Memo.doctype = 'invoice' then case when isnull(dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom, '') = '' then dbo.so_prod_tbl.en_uom_pricedef
                                                                                                                                                                                     else dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom
                                                                                                                                                                                end
                                                                                                                           else case when isnull(dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom, '') = '' then dbo.so_prod_tbl.en_uom_pricedef
                                                                                                                                     else dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom
                                                                                                                                end
                                                                                                                      end
                                                                      when dbo.dfh_v_Invoice_Memo.source = 'iave' then dbo.so_prod_tbl.en_uom_pricedef
                                                                      else ''
                                                                 end
             else ''
        end

mmackay

...just like the "Uppercase keywords" option. Thanks.

Wislon32

I like and would prefer DRBuckingham's post regarding layout of BEGIN and END as (using IF as an example, but generalised to ALL BEGIN.END

IF....BEGIN
    other code here
END

or 

IF......
BEGIN
    other code here
END

Apart from the fact that it uses less horizontal space!

p held

I would like an option to force a simple IF EXISTS() condition to ONE line.
Example:

Where 
exists ( Select 
* 
from 
@TMMSale T 
Where 
T.MaxAkey=D.a_Key ) 
and D.a_Type='I'

Instead, I would prefer the following:

Where 
exists ( Select * from @TMMSale T Where T.MaxAkey=D.a_Key ) 
and D.a_Type='I' 

I prefer this because my if exists clauses are typically short and simple and spreading it out across multiple lines seems unwarranted.

I would have figured that the option on "Subquery placement" would apply to an "If exists" clause as well.

thanks in advance.

Mattigans

Great to see so many different ideas here, knowing that the dev team is considering this stuff.

Count me in for tabs- e.g. Convert every x spaces to tab, and use tabs for alignment. Seems like we'll be seeing that in a future release for sure.

How about this... an option to align boolean operators with the previous line, so instead of:

SELECT  *
FROM    Game2
WHERE   Winner = 'Rockies'
        AND ScoreCol = 7
        AND ScoreBos = 2

We can do this:

SELECT  *
FROM    Game2
WHERE   Winner = 'Rockies'
AND     ScoreCol = 7
AND     ScoreBos = 2

I hope we're not the only ones formatting this way!

dbarchitect

There was a suggestion earlier to take out keywords like INTO for INSERT INTO and FROM in the DELETE FROM. I wouldn't want this unless it was a configured option (obviously). I love the extra keyword syntax because it offers joins right off the FROM clause (on the delete) to be properly handled and less skilled DBAs can quickly see the difference (more legible).

Suggestions are:
1) Indent the assignments, data types, or other "columns"
2) Include the column name from the schema as an alias on the field automatically
3) Format the alias to be either at the front of the column line [Alias1] = [Field1] or at the end [Field1] AS [Alias1]
4) Align the alias by = or AS keyword
5) these would also be available in the action queries, INSERT, UPDATE, or other situations where assignments are done, aligning them as a column where MAXLEN(Column1) + Tab + " = " + Columns2

Examples here:

SELECT t1.[Field1],  t1.[Field2], t1.[Field3] AS Field3Alias, Field4Alias = t1.[Field4]
FROM [dbo].[Table] as T1
WHERE t1.[Field10] = 'Something'

... looks like this ...

SELECT
	  [Field1Alias]				= t1.[Field1]
	, [Field2Alias]				= t1.[Field2]
	, [Field3AliasLongName]	 = t1.[Field3]
	, [Field4AliasLongName]	 = t1.[Field4]
FROM [dbo].[Table]
WHERE [Field10] = 'Something'

.. or this ...

SELECT
	  [Field1]			AS [Field1Alias]		
	, [Field2]			AS [Field2Alias]
	, [Field3LongName] AS [Field3AliasLongName]
	, [Field4]			AS [Field4AliasLongName]
FROM [dbo].[Table]
WHERE [Field10] = 'Something'

... INSERTs from this ...

INSERT INTO ([Field1], [Field2], [Field3], [Field4]) SELECT S1.Field1, S1.Field1, S1.Field1, S1.Field1 FROM [dbo].[SourceTable] AS S1

... to this ...

INSERT INTO (
    [Field1]
  , [Field2]
  , [Field3]
  , [Field4])
SELECT 
    [Field1]	= S1.[SourceField1]
  , [Field2]	= S1.[SourceField2]
  , [Field3]	= S1.[SourceField3]
  , [Field4]	= S1.[SourceField4]
FROM [dbo].[SourceTable] AS S1

sager

Since I often copy sql code back and forth from VBA scripts, I frequently use Ctrl-B + Ctrl-L. It comes in one long line, and it would be perfect if there was a posssibility to get my whole sql code back into one long line after I have made my changes. This would make it easier to copy back into other systems.

Best regards
Soren.

JimF

Hello,

I would like to make two suggestions.
1. As suggested by Mattigans before, I too would like to see logical operators line up under the "Where" statement.

2. How about Pascal Case for keywords?. I realize that this may require a dictionary to make it work on a user-by-user basis, but being a developer as much as a DBA I don't really care for all lowercase or all uppercase keywords. To me, "Set NoCount On" or "Select...From...Where...etc" is much more readable. If I was not using SSMS with its color coding of keywords, I might feel different, but with the colors I don't feel that I need the casing too.

Thanks for listening.

jbrown

I've seen this here already, but wanted to give a little shout out for:

1. tabs. a textpad-like "spaces to tabs" at the current tab setting (2,4, 8...)

2. how about lowercase keywords? nobody need to shout at their database


thanks, great product!

Lxocram

remove empty lines option in layout sql

willspurgeon

I second this comment.

doclane wrote:

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

willspurgeon

I RADICALLY second this comment. To put the joined table on the new line is my #1 feature request for SQL Layout. My final style ends up as:

SELECT
    F.FieldA,
    S.FieldB,
    T.FieldC 
FROM
    FirstTable F
INNER JOIN
    SecondTable S
        ON F.Key1 = S.Key1
INNER JOIN
    ThirdTable T
        ON S.Key2 = T.Key2

Having the join table on a new line and indented, and the join condition on a new line, indented again, makes it much easier for me to quickly scan the join types vs. the join tables vs. the join conditions. I would love this feature.

Thanks!

Viking Geek wrote:

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.

howarthcd

Rather than having so many options, as you'll never please everyone, how about allowing SQL Refactor to provide a piece of sample code that the user modifies to suit (perhaps through the options dialog), and then SQL Refactor could base future auto-layouts on an analysis of the modifications made to the sample code.

Chris

CarlUman

#1 for me has already been asked for a number of times...

"lowercase keywords? nobody need to shout at their database "

peterzeke

Hello:

Generally speaking I find SQL Refactor to be an awesome product -- especially when I need to refactor a coworker's script so that I can read it. I do have a feature request, however.

As a data and reporting analyst, I end up scripting complicated SQL that typically relies on joining multiple tables/views/udf tables together wherein the keys that join these objects can involve multiple keys, too.

Rather than having SQL Refactor stack the various joins on top of one another, I prefer creating a line space between each set of Join statements for easier reading. Eassentially the preferred layout is to have the JOIN keywords on one line, the object being joined on a second line, and then the Join criteria ("on...") on a third line.

For example:

SELECT 
     A.PatientName
    ,B.Provider
    ,C.LabDesc
    ,D.ResultDate
    ,D.Result

FROM
    dbo.Dim_Patients A

    LEFT JOIN
    dbo.Dim_Provider B
    on A.PatientKey = B.PatientKey
    and A.LocationKey = B.LocationKey

    LEFT JOIN
    dbo.Dim_Lab C
    on A.PatientKey = C.PatientKey
    and A.InsuranceKey = C.InsuranceKey  

    LEFT JOIN
    dbo.Fact_MeasureResults D
    on A.PatientKey = D.PatientKey
    and C.LabKey = D.LabKey

Although the above example code is actually quite brief and uncomplicated, it is nevertheless exceptionally easy to read. Consequently, I would be very appreciative if SQL Refactor could include the option to create line breaks between Join clauses, as well as the option to place joined objects and the join criteria on separate lines.

Cheers!

Paul Richmond

It would be nice to have some more options for formatting case.....

Currently Produces.....

,CASE L.[ACCT] /* Massage the account type into predetermined categories */
                              WHEN 500 THEN 'Sales'
                              WHEN 600 THEN 'COS'
                              ELSE CASE WHEN L.Sub < 75700000
                                        THEN 'Fixed Costs'
                                        WHEN L.Sub < 79099999
                                        THEN 'Variable Costs'
                                        WHEN L.Sub < 80020000
                                        THEN 'Contra Allocation'
                                        ELSE 'Units'
                                   END
                            END AS [Group]

Would like.....

,CASE L.[ACCT] /* Massage the account type into predetermined categories */
                              WHEN 500 THEN 'Sales'
                              WHEN 600 THEN 'COS'
                              ELSE CASE WHEN L.Sub < 75700000 THEN 'Fixed Costs'
                                        WHEN L.Sub < 79099999 THEN 'Variable Costs'
                                        WHEN L.Sub < 80020000 THEN 'Contra Allocation'
                                        ELSE 'Units'
                                   END
                            END AS [Group]

It appears that there are several instances where 'nested' formatting is not consistant.

Paul Richmond

I am having specific instances where long lines don't appear to wrap. Example below:

FROM    (SELECT [Description]
                               ,[Group]
                               ,[sub]
                               ,[type]
                               ,Per
                               ,AMOUNT
                         FROM   [AccountData_CTE]
                         /* The period, (1 - 24 due to budget values) is used to determine which column the amount drops into*/
                        ) P PIVOT ( SUM(P.Amount) FOR Per IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24]) ) AS Pvt
                ORDER BY [Pvt].[Group]
                       ,[Pvt].[Description]					 

Wonderful product by the way.... I LOVE your configuration dialogs.

Michael.Kriegner

First of all, thank you for the great layout functions and I really hope you can realize most/all of the requested features of all users, because all of them are very suitable for different tasks/projects.

Most/many features I need, are already implemented. But following formatting I would need for our company guidelines (especially our very special indentation for JOINs and CASEs):

DECLARE @x AS NVARCHAR( MAX ) -- watch the spaces (even with "pad the inside of parantheses with spaces" on; comment should stay in this line; remove redundant AS
DECLARE @i AS INTEGER -- replace INTEGER to INT

INSERT INTO TableX -- remove redundant INTO (if exists), name should be in the same line
(
	Column1,
	Column2,
	Column3
)
SELECT
	Table2.a,
	Table2.b,
	Table3.c
FROM Table2
 INNER JOIN Table3 -- INNER JOIN just one space after FROM
  ON Table2.ID = Table3.ID -- ON just one space after INNER JOIN
WHERE Table2.x = 1

INSERT TableX
(
	Column1,
	Column2,
	Column3
)
VALUES
(
	1,
	2,
	@x
)

DELETE FROM Database1.dbo.TableY -- remove redundant FROM (if exists), name should be in the same line
FROM Database1.dbo.TableY TableY
 INNER JOIN TableZ
  ON TableY.ID = TableZ.ID
WHERE TableZ.x = @x

SELECT ColumnA -- only one column should stay in the same line
FROM TableA
WHERE ID = 1

SELECT
	CASE 
	WHEN ColumnA = 1
	THEN 'a'
	WHEN ColumnB = 2
	THEN 'b'
	ELSE
		CASE
		WHEN ColumnC = 3
		THEN 'c'
		ELSE '?'
		END
	END AS TestCase
FROM TableA

UPDATE TableA
SET ColumnA = @x

UPDATE TableA
SET
	ColumnA = @x,
	ColumnB = 1

After layouting, I get this:

DECLARE @x NVARCHAR(MAX)
 -- watch the spaces (even with "pad the inside of parantheses with spaces" on; comment should stay in this line

INSERT
    TableX -- remove redundant INTO, name should be in the same line
    (
      Column1,
      Column2,
      Column3
    )
    SELECT
        Table2.a,
        Table2.b,
        Table3.c
    FROM
        Table2
    INNER JOIN Table3
    -- INNER JOIN just one space after FROM
        ON
        Table2.ID = Table3.ID -- ON just one space after INNER JOIN
    WHERE
        Table2.x = 1

INSERT
    TableX
    (
      Column1,
      Column2,
      Column3
    )
VALUES
    ( 1, 2, @x )

DELETE
    Database1.dbo.TableY -- remove redundant FROM, name should be in the same line
FROM
    Database1.dbo.TableY TableY
INNER JOIN TableZ
ON  TableY.ID = TableZ.ID
WHERE
    TableZ.x = @x

SELECT
    ColumnA -- only one column should stay in the same line
FROM
    TableA
WHERE
    ID = 1

SELECT
    CASE WHEN ColumnA = 1 THEN 'a'
         WHEN ColumnB = 2 THEN 'b'
         ELSE CASE WHEN ColumnC = 3 THEN 'c'
                   ELSE '?'
              END
    END AS TestCase
FROM
    TableA

UPDATE
    TableA
SET ColumnA = @x

UPDATE
    TableA
SET ColumnA = @x, ColumnB = 1

For the layout functions we also could need a developer edition with APIs including Syntax Highlighting (like you made for SQL Compare and SQL Data Compare) for our developers to integrate in our own development tools (for internal use).

Thank you very much in advance,

Michael Kriegner

MartinB

Hi it would be nice if Refactor layed out complex case statements sensibly
I would like each case on a new line and all indented the same as in the first example

e.g

SELECT  *
FROM    (
			SELECT    ROW_NUMBER() OVER ( ORDER BY 
			CASE WHEN @OrderByColumn = 'Amount'
			AND @SortDirection = 'ASC' THEN Batch.Amount
			END ASC, 
			CASE WHEN @OrderByColumn = 'Amount'
			AND @SortDirection = 'DESC' THEN Batch.Amount
			END DESC, 
			CASE WHEN @OrderByColumn = 'ClubName'
			AND @SortDirection = 'ASC' THEN ClubName
			END ASC, 
			CASE WHEN @OrderByColumn = 'ClubName'
			AND @SortDirection = 'DESC' THEN ClubName
			END DESC, 
			CASE WHEN @OrderByColumn = 'BillingReference'
			AND @SortDirection = 'ASC' THEN Batch.BillingReference
			END ASC, 
			CASE WHEN @OrderByColumn = 'BillingReference'
			AND @SortDirection = 'DESC' THEN Batch.BillingReference
			END DESC ) AS RowNumber,*
          FROM      Batch
        ) AS PageRequests
WHERE   PageRequests.RowNumber BETWEEN ( @pageIndex )
                  AND     ( @pageIndex + @pageSize )

Becomes

        SELECT  *
        FROM    (
                  SELECT    ROW_NUMBER() OVER ( ORDER BY CASE WHEN @OrderByColumn = 'Amount'
                                                                   AND @SortDirection = 'ASC' THEN Batch.Amount
                                                         END ASC, CASE WHEN @OrderByColumn = 'Amount'
                                                                            AND @SortDirection = 'DESC' THEN Batch.Amount
                                                                  END DESC, CASE WHEN @OrderByColumn = 'ClubName'
                                                                                      AND @SortDirection = 'ASC' THEN ClubName
                                                                            END ASC, CASE WHEN @OrderByColumn = 'ClubName'
                                                                                               AND @SortDirection = 'DESC' THEN ClubName
                                                                                     END DESC, CASE WHEN @OrderByColumn = 'BillingReference'
                                                                                                         AND @SortDirection = 'ASC' THEN Batch.BillingReference
                                                                                               END ASC, CASE WHEN @OrderByColumn = 'BillingReference'
                                                                                                                  AND @SortDirection = 'DESC' THEN Batch.BillingReference
                                                                                                        END DESC ) AS RowNumber,
                            *
                  FROM      Batch
                ) AS PageRequests
        WHERE   PageRequests.RowNumber BETWEEN ( @pageIndex )
                                       AND     ( @pageIndex + @pageSize )

Anu D

Thanks for your post.

I have logged this issue as a feature request (SR-690) so that it can be reviewed by our development team and incorporate in our future release.

Kindly let us know if you have any issues with SQL Refactor, I'll like to help.

MartinB

Hi

Another layout suggestion.
It would be nice if calls to stored procs used the same comma usage as insert statements etc e.g.

EXEC dbo.usp_CalledsProc @Param1, @Param2, @Param3, Param4, @Param5, @Param6,
    @Param7, @Param8, @Param9, @Param10

would become

EXEC dbo.usp_CalledsProc 
@Param1, 
						 @Param2, 
						 @Param3, 
						 @Param4, 
						 @Param5, 
						 @Param6,
						 @Param7, 
						 @Param8, 
						 @Param9, 
						 @Param10

dbbishop

I don't know if this has been brought up, but I would like to see Refractor preserve the case of object name (columns, procs, tables) when laying out existing SQL code.

Fof example, if I have a table named Products with the columns ProductID, ProductName, CostPerUnit, Unit as defined in the DML, and I have the following code:

SELECT productid, productname FROM products

After laying out the code, it would be changed to:

SELECT ProductID,
            ProductName
FROM    Products

Lxocram

ELSE IF as one operator
instead of:

IF
...
ELSE IF
        ...
        ELSE IF
                ...
                ELSE IF

statement should look like

IF
...
ELSE IF
...
ELSE IF

...
ELSE

BOOLEAN OPERATOR indentation (EDIT: Option is in Expressions/Parentheses content)

instead of

WHERE condition
           AND(condition 
           OR condition)

WHERE condition
           AND(condition 
                  OR condition)

Lxocram

An option for line comment indentation

    WHERE condition --===============
 --=  Comment here
    --=  bla bla bla
 --===============                     
                OR ....

    WHERE condition
    --===============
    --=  Comment here
    --=  bla bla bla
    --===============
               OR ....

options:

* total left * same as code * one tab left of code * new line (when code is folowed by -- place the comment on a new line * replace line comments with comment zone (multi lines of -- become /**/)