SQL AZURE – How to Disable and Enable All Constraint for Table and Database

Recently I wrote blog to enable and disable all constraints in the database SQL SERVER – How to Disable and Enable All Constraint for Table and Database.

One of my reader told me that this trick of sp_msforeachtable doesn’t work in Azure SQL Database (WASD). It is interesting that a lot of things that we assume when working with on-premise SQL Server are not completely available when working on SQL Azure because it is Database as a service. Though some of the system commands are not exposed, we can always write something using the Dynamic Management Views (DMVs) something similar to achieve the same effect.

Here is the modified version of script which can be used on SQL Azure database. You can check the same on your servers and let me know.

-- Get ALTER TABLE Script for all tables to Disable all constraints for database
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']'
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GO
-- Get ALTER TABLE Script for all tables to Enable all constraints for database
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] NOCHECK CONSTRAINT all'
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id


Note that Executing above would NOT make any change. By executing above script, there would be an output in Management Studio. This output is a set of commands that you will need to explicitly run it on the servers post that.

Let me know if you ever required such a capability when working with SQL Azure and what have you been doing to achieve the same?

Reference: Pinal Dave (https://blog.sqlauthority.com)