Red Gate forums :: View topic - Formatting requests - post them here
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Refactor 1
SQL Refactor 1 forum

Formatting requests - post them here

Search in SQL Refactor 1 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2, 3, 4, 5  Next
Author Message
Andras



Joined: 19 May 2005
Posts: 249
Location: Cambridge, UK

PostPosted: Thu Nov 02, 2006 11:00 am    Post subject: Formatting requests - post them here Reply with quote

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

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.
Back to top
View user's profile Send private message MSN Messenger
almaz



Joined: 06 Nov 2006
Posts: 1

PostPosted: Mon Nov 06, 2006 6:39 pm    Post subject: Reply with quote

In our company we are using lowercase SQL syntax. It would be nice to have an option to convert SQL statements to lowercase.
Back to top
View user's profile Send private message
JonathanWatts



Joined: 15 Dec 2004
Posts: 399
Location: Red Gate Software

PostPosted: Tue Nov 07, 2006 11:46 am    Post subject: Reply with quote

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


Last edited by JonathanWatts on Wed Nov 08, 2006 11:18 am; edited 1 time in total
Back to top
View user's profile Send private message
rdobrich



Joined: 14 Feb 2005
Posts: 40

PostPosted: Wed Nov 08, 2006 8:17 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
mrshrinkray



Joined: 13 Nov 2006
Posts: 1

PostPosted: Mon Nov 13, 2006 4:27 pm    Post subject: Reply with quote

I layout my SQL in the following way:

Code:
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.
Back to top
View user's profile Send private message
igorolv



Joined: 13 Nov 2006
Posts: 1

PostPosted: Mon Nov 13, 2006 8:46 pm    Post subject: Reply with quote

SQL Refactor is really great thing. Thank you.

But additionaly we use:
1) spaces before keywords
Code:

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


2) Nested joins to show join order
Code:
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
Back to top
View user's profile Send private message
doclane



Joined: 21 Nov 2006
Posts: 3

PostPosted: Tue Nov 21, 2006 10:53 pm    Post subject: New feature Reply with quote

Would like to align the commas with the columns
this:
Code:

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

To This:
Code:

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

_________________
Thomas M Lane
Back to top
View user's profile Send private message
doclane



Joined: 21 Nov 2006
Posts: 3

PostPosted: Tue Nov 21, 2006 11:08 pm    Post subject: Feature request Reply with quote

Indent line comments with next or previous line
This:
Code:

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:

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
Back to top
View user's profile Send private message
doclane



Joined: 21 Nov 2006
Posts: 3

PostPosted: Wed Nov 22, 2006 3:37 pm    Post subject: Bug? Reply with quote

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

Code:

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

Becomes:
Code:

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

where as my rules should have it become:
Code:

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

_________________
Thomas M Lane
Back to top
View user's profile Send private message
Bodhi



Joined: 22 Nov 2006
Posts: 6
Location: Spokane, Washington

PostPosted: Wed Nov 22, 2006 7:26 pm    Post subject: Kernighan and Ritchie code lay-out Reply with quote

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

Code:
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".
Back to top
View user's profile Send private message
Bodhi



Joined: 22 Nov 2006
Posts: 6
Location: Spokane, Washington

PostPosted: Wed Nov 22, 2006 8:36 pm    Post subject: Need better wrapping Reply with quote

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 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 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.
Back to top
View user's profile Send private message
monkeygrind



Joined: 21 Jun 2006
Posts: 28
Location: Seattle-ish

PostPosted: Mon Nov 27, 2006 11:07 pm    Post subject: Reply with quote

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:
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:
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:
insert into dbo.Company
     (companyid, locationid)
values
     (@companyid, @locationid)


instead of
Code:

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.
Back to top
View user's profile Send private message
Bodhi



Joined: 22 Nov 2006
Posts: 6
Location: Spokane, Washington

PostPosted: Mon Nov 27, 2006 11:29 pm    Post subject: Option to omit redundant, optional key words Reply with quote

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


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


Similarly get rid of the redundant FROM in DELETE FROM

Code:
DELETE FROM dbo.my_table
WHERE ...

Change to
Code:
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.
Back to top
View user's profile Send private message
DRBuckingham



Joined: 22 Sep 2005
Posts: 4
Location: Nashville, TN

PostPosted: Fri Dec 01, 2006 9:34 pm    Post subject: Joins with hints Reply with quote

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. Razz
_________________
David R Buckingham
SQL Developer
HCA Physician Services
Back to top
View user's profile Send private message
vudang



Joined: 08 Dec 2006
Posts: 1

PostPosted: Fri Dec 08, 2006 7:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2, 3, 4, 5  Next
Page 1 of 5

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group