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 upgraded my SQL Prompt recently, mostly as a habit. The team led by David and Aaron are always adding cool new features, and have almost never broken my flow, so I usually take the changes they’ve made whenever they appear. In this case, I got an unexpected surprise.
While demoing some tSQLt stuff, I wanted to show how to install the framework. I loaded the tsqlt.class.sql file and clicked Execute. What I saw was this:
In the middle of my SSMS window was a warning. I’ve got multiple items without WHERE clauses in the script. There are deletes, and in this case they don’t matter. However I got a warning. I could stop execution or execute.
This made me pause in front of the audience for 10 seconds while I read it, but I clicked “Execute anyway”, things worked, and I went on.
However, that was cool.
I tried this in other ways. Suppose I had an UPDATE without a WHERE.
Same warning. That’s a good one. What if I highlighted just part of a script?
I can’t tell you how many times I’ve done this in a presentation. Or in production, where it’s happened a few times. I could turn off the warning, but I love it. This is exactly what I need to prevent me from doing something stupid that I didn’t mean to do.
If I want to clear a table, and sometimes I do in demos, I click “Execute”. However, if I’ve made a mistake, I just hit enter, take the default, and then fix things.
I love SQL Prompt, and it’s one of my favorite tools from Redgate. I also love the development process, with the team working against submissions and requests from users, and responding with small releases hundreds of times a year.
If you’ve got SQL Prompt, be sure you update to v7.2. If not, then download it and give it a try. I think if you spend a good two weeks working with it, customizing snippets, and practicing some of they keystrokes, you’ll love it as well.
You can see a complete list of SQL Prompt tips at Redgate.
Voice of the DBA 
I’m surprised that an experienced company such as Redgate are so far behind…
SSMS Boost have been doing this for a couple of years now and thier plugin is free!
LikeLike
Glad to know I am not the only one who shot himself in the foot ( a couple of times over the years – not at the point on needing fast reloading). Only harm done was to my ego.
Should have been built-in in SSMS.
LikeLike
I’m not sure it should have been built in, as some of the warnings Windows throws I find annoying.
Glad I get the warning here, as I’ve definitely shot myself in the foot, and been restoring things in the past.
LikeLike
…and they say “you can’t fix stupid”. Well, occasionally you can. Of course, it does give you the option of “never warn again” (bad choice!), and there are some who will hit “execute anyway” without thinking, but at least it forces you to pause a moment to consider “is this the right thing to do?”. Bravo to RedGate.
LikeLike
Thanks, and you can’t stop everything. I am tempted to hit “execute anyway”, but default, but this makes me think about the script I just chose. That’s what keeps me from selecting “never warn again”.
LikeLike
I’m surprised people will blindly execute a SQL statement they don’t completely understand, relying on a parsing tool to detect inadvertent missing WHERE clauses?
SQL assumes you know what you’re doing. As it should.
I guess in the era of database code generation tools (i.e. LINQ, Entity Framework code-first projects) we’re turning loose a generation of developers that are incompetent in their back-end database platforms and I see that as a bad thing.
LikeLike
Wait, what? Blindly execute a SQL statement they don’t understand? What does that have to do with this? Executing a statement without a WHERE clause isn’t necessarily a question or talent, skill, or any other shortcoming.
It could be, but it’s also a mistake. There are plenty of people that execute statements with WHERE clauses, with well written code that they don’t understand. However since you can highlight code and execute it, and that happens when someone is busy, they can make a mistake and not highlight enough code in the heat of the moment.
This helps you to stop and catch those issues. If you meant to execute this code, you can.
LikeLike
WayOutWest spells it exactly as it happens. Even the best can make a mistake without being irremediably incompetent. #### happens.
LikeLike
Yes, this caught me by surprise after I updated RedGate – very useful addition.
Of course, if you have a script that contains a deliberate delete or update without a where clause, you can just add “where 1=1”.
That way, you don’t get into the dangerous habit of just hitting “execute anyway”…
LikeLike
That’s a great idea. where 1 = 1 where you want the code to run.
LikeLike
I wish it wouldn’t show the warning when deleting from a temp table, or table variable though.
LikeLike
That’s a great point. Have you put that on UserVoice? I’d vote for that as an option.
LikeLike
There are a couple of UserVoice requests on this (or similar) already:
https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/13796154-configurable-execution-warnings
https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/15522495-add-the-ability-to-selectively-disable-the-update
LikeLike