Cool new features in SQL Prompt

Summary

SQL Prompt has some cool new features which I'm going to show you. If you've never used SQL Prompt, or have only used it in the past, then you might not be aware of some of this new functionality.

Or, indeed, if you've already got SQL Prompt (like some of my colleagues at one of my recent contract gigs) and use it quite a bit, but you haven't noticed any cool new features (also like some of my recent colleagues), then you should definitely read on.

Disclosure

As I have mentioned elsewhere, this year I am very happy to be a Friend of Redgate. You should click the link to see what that means and, if you do, you'll notice that I get free software licenses for their tools. But you'll also notice that they claim to be interested in receiving honest feedback and I've found this to be true.

Before you close the browser tab in disgust at the prospect of reading a shill review, just stick around for a minute for me to show you some of the cool new features in SQL Prompt and then make up your own mind if you think it's any good or not.

Background

I've written (briefly) about SQL Prompt before, when I wrote about recovering unsaved queries from SQL Server Management Studio in the event of a crash. However, none of the features mentioned in this article were available back then and it seems that some of them have escaped people's attention.

I was in Cambridge in March for some reason, so I popped in to Redgate and met up with some of the SQL Prompt team and they showed me some of the cool new features they were working on. These features have now been released. I also had lunch in the very nerdily-named SQL Servery. People who know me in real life will probably imagine that this was the highlight of my visit...

SELECT * FROM SqlServery WHERE Delicious = 1. Thanks to @redgate for indulging me and being excellent hosts today 👍🏻 pic.twitter.com/L3gkhfc8Rz

— Tom Chantler (@TomChantler) March 2, 2016

So what did I see?

Execution warnings

If you're foolhardy enough to try to UPDATE or DELETE without a WHERE clause, then the new default behaviour is to warn you, like this:

If you can honestly tell me that this would never have saved you a bit of pain then my response would be that either you haven't worked with SQL very much or you're fibbing.

Results Grid Features

If you've used SQL Server Management Studio for any length of time (and therefore think the execution warnings are a good idea, but reckon that you don't need them) then the chances are that you have probably tried to copy some data from the results grid and then do something with it.

Highlight the desired data and press the right mouse button and you'll see something like this.

Copy as IN clause

If you select individual values from a single column (or click on a column heading) then right-clicking on the results grid will give you the option to Copy as IN clause.

Obviously if you've highlighted more than one column then you won't see the Copy as IN clause option as that wouldn't make sense.

It also removes duplicates, which means that if you select a column of data like this:

then selecting Copy as IN clause gives you something like this:

IN
(
    N'2016-04-20 20:08:57.100 +00:00'
)

Script as INSERT

This is great if you want to play around with data. Here is a somewhat contrived example.

This data:

Creates this new query when you right-click and Script as INSERT:

CREATE TABLE #temptable
    (
      [Column1] INT ,
      [Column2] NVARCHAR(MAX) ,
      [Column3] DATETIMEOFFSET(7)
    )
INSERT  INTO #temptable
VALUES  ( 1, N'Message 1 Text', N'2016-04-20 20:08:57.100 +00:00' ), 
        ( 2, N'Message 2 Text', N'2016-04-20 20:08:57.100 +00:00' ), 
        ( 3, N'Message 3 Text', N'2016-04-20 20:08:57.100 +00:00' )
DROP TABLE #temptable

Open in Excel

If you've ever encountered any problems copying data from the results grid into a spreadsheet, or have found the process of exporting the results to a file and importing that into Excel to be tedious, then you're going to love this feature. It's exactly as good as you'd wish. No need for any screenshots here.

Execute Current Statement

One other thing that my colleagues didn't seem to know about was the fact that you can press Shift-F5 to execute the current statement (i.e. it does the equivalent of highlighting the current command with the mouse, but doesn't accidentally leave off the WHERE clause or anything else daft like that). This is actually really pretty useful, although annoyingly it appears that it tries to parse the entire contents of the tab, so if there are any syntax errors anywhere in the query window it'll give you an error message like this (whereas highlighting the desired statement with the mouse and pressing F5 will execute it successfully):

Conclusion

SQL Prompt has been around since around 2007 and, since it gets slightly better all the time, it should come as no surprise that it's actually pretty good. Every now and then some really cool new functionality comes along, like the execution warnings and the results grid features.

Check out the release notes to see more.