Script All Database Foreign Keys

Need the DDL for every foreign key in a database - or just the ones around one table? This script uses sp_fkeys to collect all FK definitions, groups multi-column constraints by KEY_SEQ, and prints two blocks: DROP scripts (commented out) followed by full CREATE scripts including ON UPDATE / ON DELETE CASCADE rules. Set @TableName to limit it to one table.

SET NOCOUNT ON;

--You can either script all FKs, or script them referencing a specific table.
DECLARE @TableName NVARCHAR(128) --SET @TableName = 'bPOHD'

DECLARE @fFKName NVARCHAR(128)
DECLARE @fFKColumnName NVARCHAR(128)
DECLARE @fPKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fSechemaName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @fFieldNames NVARCHAR(500)
DECLARE @FTName NVARCHAR(128)
DECLARE @FKName NVARCHAR(128)
DECLARE @STName NVARCHAR(128)
DECLARE @FKType NVARCHAR(128)
DECLARE @FTFields NVARCHAR(500)
DECLARE @STFields NVARCHAR(500)

DECLARE @FKTemp TABLE
(
	PKTABLE_QUALIFIER NVARCHAR(128),
	PKTABLE_OWNER NVARCHAR(128),
	PKTABLE_NAME NVARCHAR(128),
	PKCOLUMN_NAME NVARCHAR(128),
	FKTABLE_QUALIFIER NVARCHAR(128),
	FKTABLE_OWNER NVARCHAR(128),
	FKTABLE_NAME NVARCHAR(128),
	FKCOLUMN_NAME NVARCHAR(128),
	KEY_SEQ INT,
	UPDATE_RULE INT,
	DELETE_RULE INT,
	FK_NAME NVARCHAR(128),
	PK_NAME NVARCHAR(128),
	DEFERRABILITY INT
)

DECLARE TTableNames CURSOR FOR
	SELECT
		name,
		schema_name(schema_id)
	FROM
		sys.objects
	WHERE
		type = 'U'
		AND is_ms_shipped = 0
		AND (object_id = object_id(@TableName) OR @TableName IS NULL)
OPEN TTableNames

FETCH NEXT FROM TTableNames INTO @fTableName, @fSechemaName
WHILE @@FETCH_STATUS = 0
BEGIN--WHILE
	----------------------------------------------------------------------------------------------
	INSERT @FKTemp EXEC dbo.sp_fkeys @pktable_name = @fTableName, @pktable_owner = @fSechemaName
	----------------------------------------------------------------------------------------------
	FETCH NEXT FROM TTableNames INTO @fTableName, @fSechemaName
END--WHILE
CLOSE TTableNames DEALLOCATE TTableNames

SET @fFieldNames = ''
SET @fTableName = ''

SELECT DISTINCT
	FK_NAME AS FKName,
	FKTABLE_NAME AS FTName,
	@fFieldNames AS FTFields,
	PKTABLE_NAME AS STName,
	@fFieldNames AS STFields,
	@fFieldNames AS FKType
INTO
	#FKTemp1
FROM
	@FKTemp
ORDER BY
	FK_NAME,
	FKTABLE_NAME,
	PKTABLE_NAME

DECLARE FK_CUSROR CURSOR FOR
	SELECT FKName
	FROM #FKTemp1
OPEN FK_CUSROR

FETCH FROM FK_CUSROR INTO @fFKName
WHILE @@FETCH_STATUS = 0
BEGIN--WHILE
	----------------------------------------------------------------------------------------------

	DECLARE FK_FIELDS_CUSROR CURSOR FOR
		SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
		FROM @FKTemp
		WHERE FK_NAME = @fFKName
		ORDER BY KEY_SEQ
	OPEN FK_FIELDS_CUSROR
	FETCH FROM FK_FIELDS_CUSROR INTO @fFKColumnName,@fPKColumnName,@fUpdateRule,@fDeleteRule

	WHILE @@FETCH_STATUS = 0
	BEGIN--WHILE
		----------------------------------------------------------------------------------------------
		UPDATE #FKTemp1 SET FTFields = CASE WHEN LEN(FTFields) = 0 THEN '['+@fFKColumnName+']' ELSE FTFields +',['+@fFKColumnName+']' END
		WHERE FKName = @fFKName
	
		UPDATE #FKTemp1 SET STFields = CASE WHEN LEN(STFields) = 0 THEN '['+@fPKColumnName+']' ELSE STFields +',['+@fPKColumnName+']' END
		WHERE FKName = @fFKName
		----------------------------------------------------------------------------------------------
		FETCH NEXT FROM FK_FIELDS_CUSROR INTO @fFKColumnName,@fPKColumnName, @fUpdateRule,@fDeleteRule
	END--WHILE

	UPDATE #FKTemp1 SET FKType = CASE WHEN @fUpdateRule = 0 THEN FKType + ' ON UPDATE CASCADE' ELSE FKType END
	WHERE FKName = @fFKName

	UPDATE #FKTemp1 SET FKType = CASE WHEN @fDeleteRule = 0 THEN FKType + ' ON DELETE CASCADE' ELSE FKType END
	WHERE FKName = @fFKName

	CLOSE FK_FIELDS_CUSROR DEALLOCATE FK_FIELDS_CUSROR

	----------------------------------------------------------------------------------------------
	FETCH next FROM FK_CUSROR INTO @fFKName
END

CLOSE FK_CUSROR DEALLOCATE FK_CUSROR

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

PRINT '--Drop scripts:'
PRINT '/*'
DECLARE DropCursor CURSOR FOR
	SELECT FTName, FKName, FTFields, STName, STFields, FKType
	FROM #FKTemp1
OPEN DropCursor

FETCH FROM DropCursor INTO @FTName, @FKName, @FTFields, @STName, @STFields, @FKType
WHILE @@FETCH_STATUS = 0
BEGIN--WHILE
	----------------------------------------------------------------------------------------------

	PRINT 'ALTER TABLE [' + @fSechemaName + '].[' + @FTName + '] DROP CONSTRAINT [' + @FKName + ']'

	----------------------------------------------------------------------------------------------
	FETCH FROM DropCursor INTO @FTName, @FKName, @FTFields, @STName, @STFields, @FKType
END

CLOSE DropCursor DEALLOCATE DropCursor
PRINT '*/'

PRINT ''
PRINT '--Create scripts:'

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

DECLARE ScriptCursor CURSOR FOR
	SELECT FTName, FKName, FTFields, STName, STFields, FKType
	FROM #FKTemp1
OPEN ScriptCursor

FETCH FROM ScriptCursor INTO @FTName, @FKName, @FTFields, @STName, @STFields, @FKType
WHILE @@FETCH_STATUS = 0
BEGIN--WHILE
	----------------------------------------------------------------------------------------------

	PRINT 'ALTER TABLE [' + @fSechemaName + '].[' + @FTName + '] ADD CONSTRAINT [' + @FKName + '] FOREIGN KEY (' + @FTFields + ') REFERENCES [' + @STName + '] (' + @STFields + ') ' + @FKType

	----------------------------------------------------------------------------------------------
	FETCH FROM ScriptCursor INTO @FTName, @FKName, @FTFields, @STName, @STFields, @FKType
END

CLOSE ScriptCursor DEALLOCATE ScriptCursor

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

DROP TABLE #FKTemp1