Surround Code with Comments in SQL Prompt

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool.  SQL Prompt will be yours as well if you give it a try.

I can’t believe I haven’t built this snippet before now, but it’s not in my list. I’ve dealt with this for some time, but I decided enough was enough.

Here’s what I want. Note that all the code is inside a single comment.

2018-01-27 09_28_29-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

Getting There

I often have some random notes that I want to keep in a comment. For example, I might get a list of tables like this:

2018-01-27 09_25_12-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

If I highlight this code and hit Ctrl, I get a list of snippets that contain a certain token. In my case, I get:

2018-01-27 09_26_31-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

If I select Comment, I get the code commented, but as single line quotes.

2018-01-27 09_26_58-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

That’s OK, but if I am saving something like STATISTICS output, that’s not pretty or easy for me to read. I prefer a single comment, not a series of separate inline comments. This is even more annoying with code.

Let’s fix this with a snippet. Here’s my snippet code:

2018-01-27 09_30_43-SQL Prompt - Create New Snippet

The $SELECTEDTEXT$ token will take whatever text you’ve highlighted and insert it into the token. In my case, I just want this commented out.

Let’s see how this works. Suppose I have this query:

2018-01-27 09_36_06-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

I really want to get the results from STATISTICS IO and TIME as I’m tuning a few things here and I want to check how well my changes work.

Once I run this, I’d like to place the results with the query and see them to compare with the next iteration. I paste the results into the query window like this:

2018-01-27 09_37_42-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

Now I can highlight those results and hit CTRL, type surr, and get this:

2018-01-27 09_38_12-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

I hit tab and I have my notes commented out:

2018-01-27 09_38_28-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

The next time I run the query, I can easily compare how things have changed:

2018-01-27 09_38_37-SQLQuery1.sql - (local)_SQL2016.Northwind (PLATO_Steve (66))_ - Microsoft SQL Se

I also use this when working through a list of results. I’ll get those in the query window, highlight them, and then surround them with a comment.

There are lots of places you might like to use this token with SQL Prompt. For more ideas, Phil Factor has a nice scenario for using this with other tokens in the Redgate Hub.

Give SQL Prompt a try today and see how it can improve coding and feel free to share your tips here.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to Surround Code with Comments in SQL Prompt

  1. That “surround code with comment” is a great tip! I take notes in ssms so that is a useful tip for me!


  2. Yuri Padorin says:

    Actually figured this one out this past weekend before I saw your article. Same feelings, the single line commenting was driving me nuts.


Comments are closed.