Redgate SQLPrompt Execution Warnings

What?

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:

EWDelete

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.

EWCTE

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;

EWSP

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?

EXEC dbo.SacrificeTable;
(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.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

2 Responses to Redgate SQLPrompt Execution Warnings

  1. Michael Palecek says:

    This has to be the best feature of SQL Prompt now. Having recently had to restore my db just to fix a single table I can really appreciate it.

    • thomasrushton says:

      Oh, I can relate to that.

      Well, having to restore a DB to fix a table that *someone else* broke… 😉

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s