Cool new features in SQL Prompt

Summary

SQL Prompt has some cool new features which I'm goin' to show you. If you've never used SQL Prompt, or have only used it in XXXXX 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 XXXXX 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 XXXXX Friend of Redgate. You should click XXXXX 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 receivin' honest feedback and I've found this to be true.

Before you close XXXXX browser tab in disgust at XXXXX prospect of readin' XXXXX shill review, just stick around for XXXXX minute for me to show you some of XXXXX 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 recoverin' unsaved queries from SQL Server Management Studio in XXXXX event of XXXXX crash. However, none of XXXXX 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 XXXXX SQL Prompt team and they showed me some of XXXXX cool new features they were workin' on. These features have now been released. I also had lunch in XXXXX very nerdily-named SQL Servery. People who know me in real life will probably imagine that this was XXXXX highlight of my visit...

SELECT * FROM SqlServery WHERE Delicious = 1. Thanks to @redgate for indulgin' me and bein' 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 XXXXX WHERE clause, then XXXXX new default behaviour is to warn you, like this:

If you can honestly tell me that this would never have saved you XXXXX 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 XXXXX execution warnings are XXXXX good idea, but reckon that you don't need them) then XXXXX chances are that you have probably tried to copy some data from XXXXX results grid and then do somethin' with it.

Highlight XXXXX desired data and press XXXXX right mouse button and you'll see somethin' like this.

Copy as IN clause

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

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

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

then selectin' Copy as IN clause gives you somethin' 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 XXXXX 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 copyin' data from XXXXX results grid into XXXXX spreadsheet, or have found XXXXX process of exportin' XXXXX results to XXXXX file and importin' that into Excel to be tedious, then you're goin' to love this feature. It's exactly as good as you'd wish. No need for any screenshots here.

Execute Current Statement

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

Conclusion

SQL Prompt has been around since around 2007 and, since it gets slightly better all XXXXX 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 XXXXX execution warnings and XXXXX results grid features.

Check out the release notes to see more.