Whenever I have to run an update statement, I always get nervous that I might forget the WHERE clause and overwrite the entire column in the database. Is there any setting or plugin or addon I can get for SQL Server Management Studio to block certain dangerous statements, unless you explicitly say its ok to run?

I'm working on a development server, so I can always roll back the changes, but still, I feel like I might have only the first two lines highlighted and click Execute or do something else stupid.

4 Answers 4


Create a trigger on update (another on delete is probably a good idea).

CREATE TRIGGER [Table].[uPreventUpdateOops] 
ON [Table]
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;

     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('Table') 
         AND index_id = 1)
         RAISERROR('Cannot update all rows',16,1) 

Note: This only prevents the most obvious mistake. Please do not use this on a production server as you're bound to find a statement that will totally Bork your table but pass this simple test. Also, this runs quite slow on large tables, performance will be affected.


The habits I got into very early on, after a couple whoopsie-daisy-sorry-about-your-project-tables:

  1. Begin Tran / Commit (but forgetting to commit can be just as detrimental)

  2. Write your WHERE clause first, even if it's a dummy 1=2 that will keep anything from being modified

  3. Highlight your syntax from the BOTTOM UP


You don't need a trigger to prevent you from doing something you shouldn't do. You should wrap your update in a TRANSACTION, and only commit if you are satisfied with the result.

You shouldn't be running update statements that may alter your whole db without the ability to roll back.

  • Unless the person highlights the commit portion accidentally as well.
    – datagod
    Commented Feb 11, 2015 at 5:00
  • You don't write the commit, or if you do, you wrap it in an IF that checks some condition (@@rowcount, @@error are good ones to check).
    – epic_fil
    Commented Feb 20, 2015 at 5:44

The Accidental Data Destruction Protector feature of the SSMS Tools plugin will do exactly what you want.

Also, it installs a New Query Template so that when you open a new script window, it has



as the standard query template, which I've found super helpful. You could just get in the habit of doing that, but SSMS Tools makes it really convenient.

The plugin isn't free, but it isn't expensive either, and it offers a trial period before you buy it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.