Archive

Archive for the ‘Drop Foreign Keys script’ Category

DROP Foreign keys script in a Database

May 24, 2009 Leave a comment

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

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

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

Sample Result
GO
USE [easy] ALTER TABLE [dbo].[ext_eri_dig_adn] DROP CONSTRAINT [FK_exteda_exte]
GO