Find the foreign key and Generate the drop statement

This post will help to find to the all the constraint and generate the drop statement for all foreign key constraint.

Below is the code.

DECLARE @temp TABLE (

       RowId INT PRIMARY KEY IDENTITY(1, 1)

       ,FKConstraint NVARCHAR(200)

       ,FKConstraintTblSch NVARCHAR(200)

       ,FKConstraintTbNm NVARCHAR(200)

       ,FKConstraintClNm NVARCHAR(200)

       ,PKConstraint NVARCHAR(200)

       ,PKConstraintTblSch NVARCHAR(200)

       ,PKConstraintTblNm NVARCHAR(200)

       ,PKConstraintClmNm NVARCHAR(200)

       )

INSERT INTO @temp (

       FKConstraint

       ,FKConstraintTblSch

       ,FKConstraintTbNm

       ,FKConstraintClNm

       )

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 @temp

SET PKConstraint = UNIQUE_CONSTRAINT_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.FKConstraint = R.CONSTRAINT_NAME

UPDATE @temp

SET PKConstraintTblSch = TABLE_SCHEMA

       ,PKConstraintTblNm = TABLE_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PKConstraint = C.CONSTRAINT_NAME

UPDATE @temp

SET PKConstraintClmNm = COLUMN_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PKConstraint = U.CONSTRAINT_NAME

--SELECT * FROM @temp

--DROP CONSTRAINT:

SELECT 'ALTER TABLE [' + FKConstraintTblSch + '].[' + FKConstraintTbNm + ']

DROP CONSTRAINT ' + FKConstraint + ' GO'

FROM @temp

Thx,
RS