DROP Foreign keys within a Database script

Drop Foreign key script will generate drop script for all foreign keys within a database.

use databasename
go
SET NOCOUNT ON
DECLARE @FK_Name	VARCHAR(100)
DECLARE @FK_TableName VARCHAR(100)
DECLARE @SC_Name	VARCHAR(10)
DECLARE @SQL	NVARCHAR(MAX)
DECLARE @vCount	Int
DECLARE @vNumDBs	Int
DECLARE @DB_NAME	VARCHAR(500)
DECLARE @vFKeyList	Table
	(
	SLID INT NOT NULL IDENTITY(1, 1),
	FK_Name VARCHAR(100),
	FK_TableName VARCHAR(100),
	SC_Name	VARCHAR(10)
	)
	INSERT INTO @vFKeyList
	SELECT f.name AS FK_Name,OBJECT_NAME(f.parent_object_id) AS FK_TableName,
	schema_name(f.schema_id) as SC_ID
	FROM sys.foreign_keys AS f
	INNER JOIN sys.foreign_key_columns AS fc
	ON f.OBJECT_ID = fc.constraint_object_id --where OBJECT_NAME(f.parent_object_id)='ActiveWaitStatistics'
	ORDER BY f.OBJECT_ID ASC

	Set @vNumDBs = @@RowCount
	Set @vCount = 1

	While @vCount <= @vNumDBs
		Begin
			Select @FK_Name = FK_Name,@FK_TableName=FK_TableName,
			@SC_Name=SC_Name From @vFKeyList Where SLID = @vCount
			SET @DB_NAME=DB_NAME()
			SET @SQL=''
			SET @SQL='USE [' + @DB_NAME + '] ALTER TABLE ['+ @SC_Name +'].['+ @FK_TableName + '] ' + 'DROP CONSTRAINT ['+@FK_Name+']'
			Print @SQL
			Print 'GO'
			Set @vCount = @vCount + 1
		End
SET NOCOUNT OFF

Leave a Reply

Your email address will not be published. Required fields are marked *