Is There a Failsafe for F5?

Stopping you from running ALL THE CODE

Words: 537

Time to read: ~ 2.5 minutes

Selective Execution:

Recently I came across a question in Stack Overflow (SO) that said the following:

The other day I was trying to hit another button on the menu but hit Execute – which executed the whole code and ended up deleting some tables. I have always found this scary that hitting one button can execute the whole code.

I want SQL Server to execute code only when something is selected. Is it possible? Or can SQL Server prompt before executing a query?

Can We Restrict It?

I thought this is a great question because it can be answered in 4 different ways…

1. It can be answered simply:

Just be careful with what you select and run!

2. You can get 3rd party tools to help you catch this

Red Gate, for example, even warn you three times!

GreenIsGood, no
I thought green was good…

That green “squiggle” is the new code analyser. If we move to the left, a little light-bulb will show and warn us, again, that this is bad.

 

CodeAnalysisSmall
No, stop!

Even if we ignore this, and just try to execute the query, they try a 3rd and final time to stop you!

ExecutionWarning
Seriously, you’re wearing your bad idea jeans!

3. You can comment out the query.

CommentedOut
Hit me baby 1 more time!

This will enable you to hit F5 and nothing will get executed. If you want to selectively run the code, all you need to do is highlight it.

CommentedOutHighlighted
Hit me with your rhythm stick!

And, because I have Red Gate tools, I get the lovely warning about having a DELETE with no WHERE clause again.

The problem with this is that we lose everything else available to us: Code Analysis, Parsing Information, anything! We could have accidentally typed DELETED FROM dbo.AllInLine and we’d be none the wiser until we tried to run it.

There has to be another way…

4. Is there another way?

PowerShell has the same difficulties as SSMS in this regard; this regard being the people using it anyway. You hit F8 in PowerShell, it runs the command that’s highlighted/the command of the line it’s on but if you hit F5, it runs everything!

There’s this cool method (or at least I thought it was cool) of placing the command return at the start of a PowerShell script to stop mistaken F5 clicks.

PowerShellReturn
You can divide by 0 if you don’t try to…

Can we do the same with T-SQL and SSMS? Yes, we can! Enter the command NOEXEC which, according to the docs,

Compiles each query but does not execute it.

This is great because it means that we have the ease of use like the comments method but still get the other features like Code Analyser and Parsing!

NoExec
Squiggles of glory!

Now, if we want to run the code, all we need to do is remove the first SET NOEXEC ON or highlight the code we want to run!

NoExecHighlight
Ignoring the Squiggles of glory!

SET NOEXEC Method

I like this way, seems much more professional. Plus it gives you more options that the others.

So that’s the answer that I gave!

That means I’ve hit my quota of SO questions this month!

Sweet! I can go back to learning now 🙂

 

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

3 thoughts on “Is There a Failsafe for F5?”

  1. Many moons ago I managed to wipe out every member of staff from a finance system by missing the where clause (luckily it was at a fairly quiet time and the database wasn’t particularly big so I was able to blag it as an application error while quickly restoring side by side and repopulating the table). I soon got myself into the habit of putting any DML statement into a transaction and only commit when I’m happy that the row counts are what I expect, something that I’m still doing 15 years later 🙂

    Liked by 1 person

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s