SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

SET NOCOUNT ON

DECLARE @table TABLE (
 RowId INT PRIMARY KEY IDENTITY(1, 1)
 ,ForeignKeyConstraintName NVARCHAR(200)
 ,ForeignKeyConstraintTableSchema NVARCHAR(200)
 ,ForeignKeyConstraintTableName NVARCHAR(200)
 ,ForeignKeyConstraintColumnName NVARCHAR(200)
 ,PrimaryKeyConstraintName NVARCHAR(200)
 ,PrimaryKeyConstraintTableSchema NVARCHAR(200)
 ,PrimaryKeyConstraintTableName NVARCHAR(200)
 ,PrimaryKeyConstraintColumnName NVARCHAR(200)
 )

INSERT INTO @table (
 ForeignKeyConstraintName
 ,ForeignKeyConstraintTableSchema
 ,ForeignKeyConstraintTableName
 ,ForeignKeyConstraintColumnName
 )
SELECT U.CONSTRAINT_NAME
 ,U.TABLE_SCHEMA
 ,U.TABLE_NAME
 ,U.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table
SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA
 ,PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table
SET PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT '
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
 
GO'
FROM @table

--ADD CONSTRAINT:
SELECT '
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
 
GO'
FROM @table
GO






Reference:https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

Script to generate drop all tables statements in a database in SQL Server

SELECT 'Drop table ' + NAME
FROM sys.objects
WHERE schema_name(schema_id) = 'dbo'
 AND type = 'u'

Script to drop all Foreign keys and recreate them

--Create Table and Save Foreign Keys in a Table
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

CREATE TABLE fklist (
 RowId INT PRIMARY KEY IDENTITY(1, 1)
 ,ForeignKeyConstraintName NVARCHAR(200)
 ,ForeignKeyConstraintTableSchema NVARCHAR(200)
 ,ForeignKeyConstraintTableName NVARCHAR(200)
 ,ForeignKeyConstraintColumnName NVARCHAR(200)
 ,PrimaryKeyConstraintName NVARCHAR(200)
 ,PrimaryKeyConstraintTableSchema NVARCHAR(200)
 ,PrimaryKeyConstraintTableName NVARCHAR(200)
 ,PrimaryKeyConstraintColumnName NVARCHAR(200)
 )

INSERT INTO fklist (
 ForeignKeyConstraintName
 ,ForeignKeyConstraintTableSchema
 ,ForeignKeyConstraintTableName
 ,ForeignKeyConstraintColumnName
 )
SELECT U.CONSTRAINT_NAME
 ,U.TABLE_SCHEMA
 ,U.TABLE_NAME
 ,U.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE fklist
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE fklist
SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA
 ,PrimaryKeyConstraintTableName = TABLE_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE fklist
SET PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

SELECT *
FROM fklist

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END

-----------------------------------------------------------------------------------

--Drop Foreign Keys









SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END


--Recreate Foreign Keys
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @constraint_col NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @pk_schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)
DECLARE @pk_table NVARCHAR(200)
DECLARE @pk_col NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
  ,@constraint_col = ForeignKeyConstraintColumnName
  ,@pk_schema = PrimaryKeyConstraintTableSchema
  ,@pk_table = PrimaryKeyConstraintTableName
  ,@pk_col = PrimaryKeyConstraintColumnName
 FROM fklist
 WHERE RowId = @counter

 PRINT ('ALTER TABLE [' + @schema + '].[' + @table + '] ADD CONSTRAINT ' + @constraint + ' FOREIGN KEY(' + @constraint_col + ') REFERENCES [' + @pk_schema + '].[' + @pk_table + '](' + @pk_col + ')')

 SET @counter = @counter - 1
END