Few days ago, I received code which is very similar to code shown below.
select *
from Sales.SalesOrderDetail
where ProductID > 777
I am not the guy who go crazy for formatting but I do appreciate proper coding. I like if the code was formatted like below.
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID > 777
The fastest way one can do this in SSMS is either search and replace or using SSMS short cut to covert keywords to upper case.
What I do is I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available.
Reference: Pinal Dave (https://blog.sqlauthority.com)
37 Comments. Leave new
Hi Pinal,
I tried the way and thanks for the shortcut, and CTRL+SHIFT+U is working fine but CTRL+SHIFT+L is not working for me to change the cases to lower case, it simply removes the whole line in query analyzer,
I am having SQL SERVER 2008 in MS 2003 server.
Thanks
Manish
Thats a cool and handy one!!
-Devashish
That was a handy and cool tip !!
Hi there, others shotcut then I use a lot is CTRL+R to show/hide the ResultSet (RowSet), CTRL+D to return ResultSet in a GRID and CTRL+T to return ResultSet in a text.
Yes I use those a lot. The others I use is for
commenting lines of code:
Ctrl+K,Ctrl+C
Uncommenting lines of Code: Ctrl+K,Ctrl+U
Thank you
Sir u are simply great….Thank u so much for this shortcut….
One thing i wanna ask u… i ve used the Redgate toolbelt for using intellisense in sql server 2005….There is an option for formatting the code automatically….is there some similar in built option in sql server 2008
There’s a feature in SSMS Tools Pack that will also format SQL code and uppercase only keywords, you can configure it too:
There are a lot of other helpful features in it too.
A much easier and faster way is to use the SSMS Tools Pack, it includes a query text formatting procedure that you can configure (shortcut CTRL+SHIFT+F) and it can format in uppercase (or lowercase) all keywords of your query in one pass.
This tools pack come with a lot of other features but I only use it for the text formatting shortcut and I wouldn’t live without it.
We use RedGate’s Sql Reflector for formatting purpose.
Thank you Pinal. This is a very helpful tip.
What SSMS should have is ability to uppercase only the keywords. This should not be hard for an IDE and I hope we get it in the next version. In the meantime SSMS Tools rocks.
I respectfully ask why it is necessary to use upper case for keywords when keywords automatically appear in blue.
I understand that many developers use upper case for keywords but I also know many developers who use lower case.
Is this issue not simply a matter of personal style?
Best wishes,
Keith.
Mr. Keith,
Upper case and Lower case are needed to be taken care in the cases like you are supporting the data to be entered and read in two different languages for one application.
In my case, we made an application for our client which accepts ENGLISH and ARABIC.
If we wouldn’t use “UPPERCASE” (for table names,column names) that will not execute the query at all. This is one case where we need to give importance for case sensitivity.
Aditya – Thanks for sharing it.
There’s one cool feature if u have quickly change the case of block of text in multiple line.
Simply hold down “Alt” key and make a rectangular selection. Now the you quickly modify the case for the text selected in the block.
Have a look at Instant SQL Formatter
Hi, I am using SQL SERVER 2008. But this functionality is not working in my system . while i use crtl + Shift + U, The whole query will become upper case. Is there any settings available.
First time user can go with SSMS Menu and look there, shortcuts are mentioned for every menu command. Try to remeber that and keep a pratice using them.
You will find lots of shorcut in SSMS menus.
Very Nice Tip Pinal
in response to Keith’s post – the reason that upper casing is used is that .SQL files can be saved and edited in a text editor such as notepad – this won’t pick up the formatting and automatically hightlight keywords in blue – if they’re in upper case thay stand out.
I like mine the other way around. And when is sql going to start allowing the from statement to be placed first? I think ansi sql needs to incorporate this because it would really make intellisence a lot easier.
I start with a generic statement:
SELECT *
FROM table t
After that, I just go back and write column names with the table alias (otherwise it is a huge list of columns to choose from):
SELECT t.col1, t.col2
FROM table t
Thank you for the shortcuts. I went the other way around, going crazy with all the caps that I had to convert it all to lower case and make it indent. Anyway, thanks for the CTRL-SHIFT-L, that works great.
really this shortcut save my time for deleting and writing again the keywords