Drop Foreign Keys on a Column

Need to drop every foreign key constraint that references a particular table column? This stored procedure finds all FK constraints whose parent column matches the given table and column, and drops each one. Useful before altering or dropping a column that other tables point at.

Example: EXEC DropForeignKey @Table = 'Customers', @Column = 'CustomerID'

-- =============================================
-- Author:		Josh Patterson
-- Create date: 8/15/2006
-- Description:	Drops any existing foreign keys from a specified tables column.
-- =============================================
CREATE PROCEDURE [DropForeignKey]
	@Table	VarChar(250),
	@Column	VarChar(250)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Schema_Name	VarChar(255)
	DECLARE @Table_Name		VarChar(255)
	DECLARE @Column_Name	VarChar(255)
	DECLARE @Rel_Obj_Name	VarChar(255)
	DECLARE @Rel_Type_Name	VarChar(255)
	DECLARE @Rel_Type_Desc	VarChar(255)

	DECLARE ColInfo CURSOR FOR
		WITH Rel_Objs (Obj_Name, Type_Name, Type_Desc, Parent_Object_ID, Parent_Column_ID) AS
		(
			SELECT
				f.Name,
				f.Type,
				f.Type_Desc,
				f.Parent_Object_ID,
				fc.Parent_Column_ID
			FROM
				Sys.Foreign_Keys AS f -- Foreign Key Constraints
			JOIN Sys.Foreign_Key_Columns AS fc
				ON fc.Constraint_Object_ID = f.Object_ID
				AND fc.Parent_Object_ID = f.Parent_Object_ID
		),

		Rel_Objs_Det(Schema_Name, Table_Name, Column_Name, Rel_Obj_Name, Rel_Type_Name, Rel_Type_Desc) AS
		(
			SELECT
				s.Name, o.Name, c.Name, r.Obj_Name, r.Type_Name, r.Type_Desc
			FROM
				Rel_Objs AS r -- Names for the Table/Column to query for
			JOIN Sys.Objects AS o
				ON r.Parent_Object_ID = o.Object_ID
			JOIN Sys.Columns AS c
				ON c.Object_ID = o.Object_ID
				AND c.Column_ID = r.Parent_Column_ID
			JOIN Sys.Schemas AS s
				ON s.Schema_ID = o.Schema_ID
		)

		SELECT
			r.Schema_Name,
			r.Table_Name,
			r.Column_Name,
			r.Rel_Obj_Name,
			r.Rel_Type_Name,
			r.Rel_Type_Desc
		FROM
			Rel_Objs_Det AS r
		WHERE
			r.Table_Name = @Table
			AND r.Column_Name = @Column
			AND r.Rel_Type_Name = 'F'

	OPEN ColInfo

	FETCH FROM ColInfo INTO
		@Schema_Name,
		@Table_Name,
		@Column_Name,
		@Rel_Obj_Name,
		@Rel_Type_Name,
		@Rel_Type_Desc

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		----------------------------------------------------------------------------------------------------------------

		EXEC('ALTER TABLE [' + @Table_Name + '] DROP CONSTRAINT [' + @Rel_Obj_Name + ']')

		----------------------------------------------------------------------------------------------------------------
		FETCH FROM ColInfo INTO
			@Schema_Name,
			@Table_Name,
			@Column_Name,
			@Rel_Obj_Name,
			@Rel_Type_Name,
			@Rel_Type_Desc
	END

	CLOSE ColInfo DEALLOCATE ColInfo

END
GO