How to disable all Foreign Key contraints

Here is a script that will loop through all tables in your database and disable the foreign key constraints.

DECLARE @TableName nvarchar(256)
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped' ) = 0)

    IF (@TableName <> '')
    BEGIN
        EXEC ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
        PRINT ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
    END


END
GO

posted @ Wednesday, August 7, 2013 5:43 PM

Print

Comments on this entry:

No comments posted yet.

Your comment:



 (will not be displayed)


 
 
 
Please add 1 and 8 and type the answer here:
 

Live Comment Preview: