These procedures are very useful when you need to load bulk data and you know the data is correct -- but you do not need the foreign keys and check constraints to interfere. You might also use them
So, the following procedure will disable/enable all constraints for a given parametric table:
CREATE PROCEDURE sp_ConstraintState @TblName VARCHAR(128), @State BIT = 1 AS DECLARE @SQLState VARCHAR(500) IF @State = 0 BEGIN SET @SQLState = 'ALTER TABLE '+ @TblName + ' NOCHECK CONSTRAINT ALL' END ELSE BEGIN SET @SQLState = 'ALTER TABLE ' + @TblName + ' CHECK CONSTRAINT ALL' END EXEC (@SQLState) go
exec sp_ConstraintState 'products',0
...will disable all constraints on the products table in the Northwind database.
The following procedure will use sp_constraintState to disable all constraints in the database:
Create proc sp_disable_all_const_in_db as 'exec sp_MsForEachTable 'sp_ConstraintState ''?'',0 go
For example, to disbale all constraints in the database:
About the author
Eli Leiba works at the Israel Electric Company as a senior application DBA in Oracle and Microsoft SQL Server. He has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in computer science and has 11 years of experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and development courses at the Microsoft CTEC and serves as a senior database consultant for several Israeli start-up companies.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
This was first published in February 2005