How to Disable All Constraints or Triggers in a Single Command

From time to time we all make mistakes, no DBA is immune from creating a total disaster with the click of a button.  Luckily today this was on a testing database and not on a production one, but a table hint out of place, resulted in the loss of a 90GB database table in less than a few seconds.  What started as a simple data purge ended up being nothing short of a disaster, albeit on a test database. 

The idea was to purge roughly 80GB of the data from this table, so rather than issue a long running set based delete, I opened the table in the SSMS designer and made a change to one of the columns, then scripted the operation to a new window.  I did this so that I could replace the dynamic SQL Statement with a statement to grab the rows to keep.  The problem was I put the WITH (HOLDLOCK, TABLOCKX) after my WHERE clause.  Since this is dynamic SQL, the syntax will pass a syntax check, but when the code actually executes you get an exception, and no data is copied to the tmp_Tablename table from the original base table, and then the original base table is dropped, leaving you with a wonderfully empty new table.

So on to fixing the problem and the purpose of this post.  To solve the problem, I used an integration package to pull the data from the production database table into a flat file which I then could import back into the testing server.  The problem came when I went to load the data, that check constraints failed, and the load failed.  What to do??  Well, it turns out that disabling all constraints and triggers in database is really simple, you just need to use the undocumented sp_msforeachtable stored procedure as follows:

--Disable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

--Disable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 

-- Load data Now

--Enable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

--Enable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

this allowed for the data to be loaded, and then I was able to resolve the orphaned records as needed.