Generate Script for all Foreign Keys in a Database

The below script will generate create script for all foreign keys in a database. Variable @NoCheckVal will serve the purpose if u wants to create the script with NO CHECK option.
Create the stored procedure in the target database.

CREATE PROCEDURE Usp_dba_GenFKScript (@NoCheckVal char(2))
AS
BEGIN
SET NOCOUNT ON
DECLARE @FK_Name VARCHAR(100)
DECLARE @FK_TableName VARCHAR(100)
DECLARE @FK_ColumnName VARCHAR(50)
DECLARE @RF_TableName VARCHAR(100)
DECLARE @RF_ColumnName VARCHAR(50)
DECLARE @SC_Name VARCHAR(10)
DECLARE @NoCheck Char(2)
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),
FK_ColumnName VARCHAR(50),
RF_TableName VARCHAR(100),
RF_ColumnName VARCHAR(50),
SC_Name VARCHAR(10)
)

SET @DB_NAME=DB_NAME()
INSERT INTO @vFKeyList
SELECT f.name AS FK_Name,OBJECT_NAME(f.parent_object_id) AS FK_TableName,COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS FK_ColumnName,OBJECT_NAME (f.referenced_object_id) AS RF_TableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS RF_ColumnName,
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
If @NoCheckVal is null
BEGIN
SET @NoCheck=”
END
ELSE
SET @NoCheck=@NoCheckVal

While @vCount <= @vNumDBs

Begin
Select @FK_Name=FK_Name,@FK_TableName=FK_TableName,
@FK_ColumnName=FK_ColumnName, @RF_TableName=RF_TableName,
@RF_ColumnName=RF_ColumnName,@SC_Name=SC_Name
FROM @vFKeyList where SLID= @vCount

SET @SQL=’USE [‘ + @DB_NAME + ‘] ALTER TABLE [‘+ @SC_Name +’].[‘+ @FK_TableName +’] WITH ‘ + @NoCheck +’CHECK ADD CONSTRAINT [‘+@FK_Name+’] FOREIGN KEY([‘+@FK_ColumnName+
‘]) REFERENCES [‘+@SC_Name+’].[‘+@RF_TableName+’] ([‘+@RF_ColumnName+’])’

Print @SQL
Print ‘GO’

SET @SQL=’USE [‘ + @DB_NAME + ‘] ALTER TABLE [‘+ @SC_Name +’].[‘+ @FK_TableName +’] CHECK CONSTRAINT [‘+@FK_Name+’]’

Print @SQL
Print ‘GO’ 

Set @vCount = @vCount + 1
End
SET NOCOUNT OFF
END

 

Script Sample Without NO Check option

EXECUTE Usp_dba_GenFKScript ”

USE [easy] ALTER TABLE [dbo].[ext_acdposition] WITH   CHECK ADD CONSTRAINT [FK_extacdp_swext] FOREIGN KEY([switch_extension]) REFERENCES [dbo].[switch_extension] ([switch_device])

GO

 

USE [easy] ALTER TABLE [dbo].[ext_acdposition] CHECK CONSTRAINT [FK_extacdp_swext]

GO

Script Sample With No Check option

EXECUTE Usp_dba_GenFKScript ‘NO’

USE [easy] ALTER TABLE [dbo].[ext_acdposition] WITH  NOCHECK ADD CONSTRAINT [FK_extacdp_swext] FOREIGN KEY([switch_extension]) REFERENCES [dbo].[switch_extension] ([switch_device])
GO
USE [easy] ALTER TABLE [dbo].[ext_acdposition] CHECK CONSTRAINT [FK_extacdp_swext]
GO