Formatting requests - post them here

Andras BAndras B Posts: 249
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
András Belokosztolszki, PhD
Red Gate Software Ltd.
«1

Comments

  • In our company we are using lowercase SQL syntax. It would be nice to have an option to convert SQL statements to lowercase.
  • JonathanWattsJonathanWatts Posts: 418
    edited November 8, 2006 5:18AM
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • 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
  • 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.
  • 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
    
  • 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
    
    Thomas M Lane
  • 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
    
    Thomas M Lane
  • 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 
    
    Thomas M Lane
  • 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".
  • 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 [email protected]_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.
  • 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.
  • 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.
  • 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
    David R Buckingham
    SQL Developer
    HCA Physician Services
  • 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
  • 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
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • 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
    
  • 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!
  • 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.
  • 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.
  • 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.
  • 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
  • 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!
  • 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.
  • GaryJFGaryJF Posts: 6 Bronze 2
    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!
  • 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;
    
  • 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
    R K
  • 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)
  • 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.
  • 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...
  • 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 ([email protected])>0
    

    Place operator on line by itself would look like this:
    SELECT
        (grossSalary+2000)/100
    FROM
        personnel
    WHERE
        (
         empType='m'
         OR 
         empType='n'
        )
        AND 
        ([email protected])>0
    
Sign In or Register to comment.