Formatting requests - post them here

Refactors and formats SQL code.

Formatting requests - post them here

Postby Andras » Thu Nov 02, 2006 11:00 am

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.
Andras
 
Posts: 249
Joined: Thu May 19, 2005 5:07 pm
Location: Cambridge, UK

Postby almaz » Mon Nov 06, 2006 6:39 pm

In our company we are using lowercase SQL syntax. It would be nice to have an option to convert SQL statements to lowercase.
almaz
 
Posts: 1
Joined: Mon Nov 06, 2006 6:36 pm

Postby JonathanWatts » Tue Nov 07, 2006 11:46 am

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
Last edited by JonathanWatts on Wed Nov 08, 2006 11:18 am, edited 1 time in total.
Jonathan Watts

-Project Manager
-Red Gate Software Ltd
JonathanWatts
 
Posts: 401
Joined: Wed Dec 15, 2004 11:05 am
Location: Red Gate Software

Postby rdobrich » Wed Nov 08, 2006 8:17 am

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
rdobrich
 
Posts: 40
Joined: Mon Feb 14, 2005 12:43 pm

Postby mrshrinkray » Mon Nov 13, 2006 4:27 pm

I layout my SQL in the following way:

Code: Select all
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.
mrshrinkray
 
Posts: 1
Joined: Mon Nov 13, 2006 4:23 pm

Postby igorolv » Mon Nov 13, 2006 8:46 pm

SQL Refactor is really great thing. Thank you.

But additionaly we use:
1) spaces before keywords
Code: Select all
select F1,
       F2,
  from Table1
 where F1 > 0
   and F2 < 1


2) Nested joins to show join order
Code: Select all
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
igorolv
 
Posts: 1
Joined: Mon Nov 13, 2006 8:29 pm

New feature

Postby doclane » Tue Nov 21, 2006 10:53 pm

Would like to align the commas with the columns
this:
Code: Select all
SELECT
    a.loanid
  , b.businessdate
  , customername = RTRIM(f.lastname) + ', ' + RTRIM(firstname)
  , c.loannumber

To This:
Code: Select all
SELECT
    a.loanid
    , b.businessdate
    , customername = RTRIM(f.lastname) + ', ' + RTRIM(firstname)
    , c.loannumber
Thomas M Lane
doclane
 
Posts: 3
Joined: Tue Nov 21, 2006 10:43 pm

Feature request

Postby doclane » Tue Nov 21, 2006 11:08 pm

Indent line comments with next or previous line
This:
Code: Select all
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:
Code: Select all
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
doclane
 
Posts: 3
Joined: Tue Nov 21, 2006 10:43 pm

Bug?

Postby doclane » Wed Nov 22, 2006 3:37 pm

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

Code: Select all
    DELETE FROM dbo.tbHistoricalDBFileInfo WHERE statdate < GETDATE() - 20

Becomes:
Code: Select all
    DELETE
    FROM dbo.tbHistoricalDBFileInfo
    WHERE
        statdate < GETDATE() - 20

where as my rules should have it become:
Code: Select all
    DELETE
    FROM
         dbo.tbHistoricalDBFileInfo
    WHERE
        statdate < GETDATE() - 20
Thomas M Lane
doclane
 
Posts: 3
Joined: Tue Nov 21, 2006 10:43 pm

Kernighan and Ritchie code lay-out

Postby Bodhi » Wed Nov 22, 2006 7:26 pm

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

Code: Select all
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
 
Posts: 6
Joined: Wed Nov 22, 2006 7:01 pm
Location: Spokane, Washington

Need better wrapping

Postby Bodhi » Wed Nov 22, 2006 8:36 pm

I have my Wrap text limit set to 78 characters. When I reformat with Lay Out SQL the lines don't wrap properly. Example.
Code: Select all
   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---
Code: Select all
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.
Bodhi
 
Posts: 6
Joined: Wed Nov 22, 2006 7:01 pm
Location: Spokane, Washington

Postby monkeygrind » Mon Nov 27, 2006 11:07 pm

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:
Code: Select all
insert  into dbo.Promotion
        select  @promoID,
-- other params removed for brevity
                getdate()
        from    dbo.Promotion
        where   promoID = @oldPromoID


But what I'd expect is:

Code: Select all
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

Code: Select all
insert into dbo.Company
     (companyid, locationid)
values
     (@companyid, @locationid)


instead of
Code: Select all
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.
monkeygrind
 
Posts: 28
Joined: Wed Jun 21, 2006 10:57 pm
Location: Seattle-ish

Option to omit redundant, optional key words

Postby Bodhi » Mon Nov 27, 2006 11:29 pm

I would like an option to get rid of the redundant INTO in
Code: Select all
INSERT INTO dbo.my_table (...)


Change the above to
Code: Select all
INSERT dbo.my_table (...)


Similarly get rid of the redundant FROM in DELETE FROM

Code: Select all
DELETE FROM dbo.my_table
WHERE ...

Change to
Code: Select all
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.
Bodhi
 
Posts: 6
Joined: Wed Nov 22, 2006 7:01 pm
Location: Spokane, Washington

Joins with hints

Postby DRBuckingham » Fri Dec 01, 2006 9:34 pm

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
DRBuckingham
 
Posts: 2
Joined: Thu Sep 22, 2005 3:44 pm
Location: Nashville, TN

Postby vudang » Fri Dec 08, 2006 7:57 pm

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
 
Posts: 1
Joined: Fri Dec 08, 2006 7:51 pm

Next

Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests