SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...