Have you never run a DELETE or an UPDATE without a WHERE clause by accident? Was it on Production data, on the busiest day of the year? Actually, y’know what, that doesn’t matter. Even it happened in the middle of the quietest part of the working week, there’s still that heart-thumping moment of realisation, that wondering about if your CV is up to date, and the feeling that maybe you ought to change your grots or find the defibrillator.
There are ways to try to prevent this – you could try wrapping all your code up with BEGIN TRAN / ROLLBACK, and convert the ROLLBACK to a COMMIT when you’re sure it’s right, but that still doesn’t prevent you from accidentally failing to write that. Which is where Mladen Prajdic’s SSMS Tools Pack comes in handy as it automatically creates new query windows with BEGIN TRAN / ROLLBACK statements written in them for you. But that doesn’t prevent you from selecting part of the query, failing to select the BEGIN TRAN statement, deleting the transaction wrappers or just hitting Shift+F5 on the offending statement (if you’re still stuck on an older version of SQLPrompt – why aren’t you updating it? The Prompt team puts out new versions at an alarming rate!) (Shift+F5, for those who are SQLPrompt-less, will run the T-SQL statement that’s got the cursor in it. Great bit of functionality, but in this circumstance, maybe a bit dangerous.)
So what has Redgate done? They’ve gone and given us some protection.
Here’s some test code:
USE tempdb; CREATE TABLE dbo.SacrificialTable ( id INT IDENTITY , sometext VARCHAR(50) ); DELETE FROM dbo.SacrificialTable;
Here’s what happens when you run that:
Great. Not sure about the “Don’t show this warning again” bit, but nice warning – even for a table in tempdb…
Let’s just have a little play… Change to a temporary table (one of each sort):
USE tempdb; CREATE TABLE #SacrificialTable ( id INT IDENTITY , sometext VARCHAR(50) ); DELETE FROM #SacrificialTable; CREATE TABLE ##SacrificialTable ( id INT IDENTITY , sometext VARCHAR(50) ); DELETE FROM ##SacrificialTable;
No warning message. Why not? Well, it’s a temporary table, so perhaps Redgate decided you didn’t need to know.
And, for fun, a table variable:
DECLARE @SacrificialTable TABLE ( id INT IDENTITY , sometext VARCHAR(50) ); DELETE FROM @SacrificialTable;
Again, no warning. Less of a surprise here.
OK, so what about a CTE?
CREATE TABLE dbo.SacrificialTable ( id INT IDENTITY , sometext VARCHAR(50) ); WITH someCTE AS ( SELECT id , sometext FROM dbo.SacrificialTable ) DELETE FROM someCTE;
Redgate’s got you covered.
What about creating or altering a Stored Procedure?
USE tempdb; CREATE TABLE dbo.SacrificialTable ( id INT IDENTITY , sometext VARCHAR(50) ); GO CREATE PROCEDURE SacrificeTable AS BEGIN DELETE FROM dbo.SacrificialTable; END;
Which seems a bit odd, given that you’re not actually executing the code at this point, merely creating the SP that will execute the non-filtered delete statement. But, y’know, whatever.
And actually calling the SP that doesn’t have a WHERE clause?
(327410 row(s) affected)
Ah. Bad news there – that’ll execute just fine.
And it won’t protect you against Dynamic SQL either:
DECLARE @sql VARCHAR(MAX) = 'DELETE FROM tempdb.dbo.SacrificialTable'; EXEC (@sql)
(0 row(s) affected)
Still, it’s a whole heap more protection than you used to have, and could easily justify the cost of SQLPrompt on its own. With one query prevented. Yeah, it’s that big a deal.
And for some more bad news – currently, there’s no way of configuring this thing to, say, ignore anything in tempdb or inside a stored procedure definition, or to increase the level of alerts so you get the warnings on temporary tables. But, hey, they’re temporary tables. But if you think this stuff should be a bit more configurable, I’ve put a request on Redgate’s Uservoice page to improve the functionality of the execution warnings – feel free to go in and vote it up or add your own thoughts.