When writing SQL, you can use SQL Prompt to quickly jump to some really handy formatting and refactoring actions.
This little button right here is called the Actions List and it contains over 20 functions and snippets you can use in just a few keystrokes. Simply highlight your block of code and hit Ctrl, or click on the button to open up the list.
In this blog, I’m going to tell you about five functions on the list you might not have come across, which I guarantee will save you a few more minutes each day, every day. Enough for an extra coffee or two!
1. Add quotes and commas
Say you’re copying a list of items to insert data into a table, you can save yourself time manually adding quotes and commas by selecting the block of text, hitting Ctrl to bring up the Actions List, and selecting Add quotes & commas. No more coding in Excel or repetitive quote, comma, arrow. You can see it in action in this short video:
2. ‘Surround-with’ snippets like BEGIN…END
‘Surround-with’ snippets allow you to enclose a group of SQL statements into a logical block of code, such as a BEGIN…END block, or a TRY…CATCH block. These snippets utilise the
$SELECTEDTEXT$ placeholder, so highlight your block of code, hit Ctrl and type BE or TC followed by tab, and it will surround your code block with the necessary SQL.
You can also create your own custom surround-with snippets utilising the
$SELECTEDTEXT$ placeholder in the Snippet Manager. For example:
You can then access these from the Actions List.
3. Commenting out code
To quickly comment out lines of code you want to omit while testing or troubleshooting a query, highlight the code, press Ctrl and select Comment. This will comment out each line, and can be easily reversed by selecting Uncomment from the Actions List.
4. Unformat SQL
Perhaps you have some SQL as a string in your application code, and you want to copy it out of Visual Studio to optimize it more easily inside SQL Server Management Studio. SQL Prompt can make life a little easier. Copy and paste the SQL, and hit Ctrl K, Y to format your code to your own style. Make your changes, then once you’re done, highlight the code, hit Ctrl and select Unformat. This will remove any carriage returns, tabs, extra spaces and so on, and compact the script as much as possible, ready for you to paste back into Visual Studio.
This example was also featured in the Tip #11 video above.
5. Remove comments
While writing INSERT INTO statements, if you press Tab after entering the table name, SQL Prompt will helpfully add comments to remind you of the data types and sizes you need to provide for each column:
While this is useful during development, you may well want or need to remove them again before committing the code into source control. It’s wasted time manually removing each comment, which is where SQL Prompt steps in. Highlight the code, and select Remove comments from the Actions List. It will strip out the comments automatically and, once again, save you a few extra minutes – especially handy if you’re dealing with a large script.
BONUS TIP – Disable formatting for selected text
Okay, I said I’d give you five tips, but this is a brand new function only available in the 7.3 beta so I’ll give you a sneak preview.
You might have bulk inserts that shouldn’t be wrapped, or complex SQL that is easier to understand if formatted in a logical, rather than syntactical, manner. SQL Prompt lets you highlight a block of code and select Disable formatting for selected text in the Actions List. It will enclose the selected code with comments:
This code now won’t be formatted when you run Format SQL (Ctrl K, Y).
More useful shortcuts
You’ll find lots of other useful shortcuts and snippets via the Actions List, so take a look and see for yourself. In just a few keystrokes you can Encapsulate as stored procedure – Data Platform MVP, Ike Ellis, explains how useful this is for quick refactoring in his short video, Tip #12. You can also expand wildcards via the Actions List, and Data Platform MVP, Steve Jones, will tell you why that’s good practise when writing SQL, in Tip #6.
Learn more and share
You’ll find many more quick tips like this in our Library of Super SQL Tips from Data Platform MVPs and other SQL Server experts.
If you’ve learnt something new, share it with fellow SQL professionals via the sharing buttons below. Or if you’ve got a colleague or friend new to SQL Prompt, remember there’s a 4-minute introduction video they can watch too.
Also in Blog
DevOps is moving into the mainstream, and the database is now being included, but what still holds some organizations back is the thorny issue of calculating the Return on Investment. If you’re a de...
Also in Database development
In the last few years, Black Friday and Cyber Monday have become synonymous with both getting a good bargain and causing retail pandemonium. However, times are changing. News items about people queuei...
Also about SQL Prompt
"Have you seen this new feature that auto-fills the GROUP BY with non-aggregated columns!" exclaimed my co-worker, soon after she had installed a new version of SSMS. I hadn't, but I was intrigued. I ...