USE master
GO
IF OBJECT_ID('sp_FK_script') IS NULL EXEC ('CREATE PROCEDURE sp_FK_script AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_FK_script
@Table SYSNAME,
@CreateRed VARCHAR(MAX) = NULL OUTPUT,
@DropRed VARCHAR(MAX) = NULL OUTPUT,
@CreateRing VARCHAR(MAX) = NULL OUTPUT,
@DropRing VARCHAR(MAX) = NULL OUTPUT,
@IncludeIfExists BIT = 1,
@UseGoBatchTerminator BIT = 1,
@PrintSQL BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Object_Id INT = OBJECT_ID(LTRIM(RTRIM(@Table)))
DECLARE @BatchTerminator VARCHAR(30) = CASE WHEN @UseGoBatchTerminator = 1 THEN '
GO
' ELSE '
'
END
IF @Object_Id IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_FK_script',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT 'GO
'
RETURN 30
END ;
WITH fkey
AS (SELECT TOP 999999
QUOTENAME(s2.name) AS RingSchema,
QUOTENAME(t2.name) AS RingTable,
QUOTENAME(s1.name) AS RedSchema,
QUOTENAME(t1.name) AS RedTable,
CASE WHEN t2.OBJECT_ID = @Object_Id THEN 1
ELSE 0
END AS IsRing,
QUOTENAME(fs.name) AS ForeignKey,
fs.is_disabled,
RingColumns,
RedColumns
FROM sys.schemas s1 (NOLOCK)
INNER JOIN sys.tables t1 (NOLOCK) ON s1.SCHEMA_ID = t1.SCHEMA_ID
INNER JOIN sys.foreign_keys fs (NOLOCK) ON fs.referenced_object_id = t1.OBJECT_ID
INNER JOIN sys.tables t2 (NOLOCK) ON t2.OBJECT_ID = fs.parent_object_id
INNER JOIN sys.schemas s2 (NOLOCK) ON s2.SCHEMA_ID = t2.SCHEMA_ID
CROSS APPLY (SELECT Util.dbo.StringConcat(QUOTENAME(RingColumn), ', ') AS RingColumns,
Util.dbo.StringConcat(QUOTENAME(RedColumn), ', ') AS RedColumns
FROM (SELECT TOP 999999
pc.name AS RingColumn,
rc.name AS RedColumn
FROM sys.foreign_key_columns fc (NOLOCK)
INNER JOIN sys.columns pc (NOLOCK) ON pc.OBJECT_ID = fs.parent_object_id
AND fc.parent_column_id = pc.column_id
INNER JOIN sys.columns rc (NOLOCK) ON rc.OBJECT_ID = fs.referenced_object_id
AND fc.referenced_column_id = rc.column_id
WHERE fc.constraint_object_id = fs.OBJECT_ID
ORDER BY fc.constraint_column_id) K) fc
WHERE t1.OBJECT_ID = @Object_Id
OR t2.OBJECT_ID = @Object_Id
ORDER BY IsRing,
s2.name,
t2.name,
s1.name,
t1.name,
fs.name,
fs.is_disabled)
SELECT @CreateRed = ISNULL(@CreateRed + @BatchTerminator, '')
+ (SELECT Util.dbo.StringConcat(kk.AddCons, @BatchTerminator) + @BatchTerminator
FROM (SELECT CASE WHEN @IncludeIfExists = 1 THEN 'IF OBJECT_ID(''' + RingSchema + '.' + ForeignKey + ''') IS NULL
' ELSE ''
END + 'ALTER TABLE ' + RingSchema + '.' + RingTable + CASE WHEN is_disabled = 1 THEN ' WITH NOCHECK'
ELSE ''
END + ' ADD CONSTRAINT ' + ForeignKey + ' FOREIGN KEY (' + RingColumns
+ ') REFERENCES ' + RedSchema + '.' + RedTable + ' (' + RedColumns + ')' + CASE WHEN is_disabled = 1
THEN @BatchTerminator + 'ALTER TABLE '
+ RingSchema + '.' + RingTable
+ ' NOCHECK CONSTRAINT ' + ForeignKey
ELSE ''
END AS AddCons
FROM fkey
WHERE IsRing = 0) kk),
@DropRed = ISNULL(@DropRed + @BatchTerminator, '')
+ (SELECT Util.dbo.StringConcat(kk.DropCons, @BatchTerminator) + @BatchTerminator
FROM (SELECT DISTINCT
CASE WHEN @IncludeIfExists = 1 THEN 'IF OBJECT_ID(''' + RingSchema + '.' + ForeignKey + ''') IS NOT NULL
' ELSE ''
END + 'ALTER TABLE ' + RingSchema + '.' + RingTable + ' DROP CONSTRAINT ' + ForeignKey AS DropCons
FROM fkey
WHERE IsRing = 0) kk),
@CreateRing = ISNULL(@CreateRing, @BatchTerminator)
+ (SELECT Util.dbo.StringConcat(kk.AddCons, @BatchTerminator) + @BatchTerminator
FROM (SELECT CASE WHEN @IncludeIfExists = 1 THEN 'IF OBJECT_ID(''' + RingSchema + '.' + ForeignKey + ''') IS NULL
' ELSE ''
END + 'ALTER TABLE ' + RingSchema + '.' + RingTable + CASE WHEN is_disabled = 1 THEN ' WITH NOCHECK'
ELSE ''
END + ' ADD CONSTRAINT ' + ForeignKey + ' FOREIGN KEY (' + RingColumns
+ ') REFERENCES ' + RedSchema + '.' + RedTable + ' (' + RedColumns + ')' + CASE WHEN is_disabled = 1
THEN @BatchTerminator + 'ALTER TABLE '
+ RingSchema + '.' + RingTable
+ ' NOCHECK CONSTRAINT ' + ForeignKey
ELSE ''
END AS AddCons
FROM fkey
WHERE IsRing = 1) kk),
@DropRing = ISNULL(@DropRing + '
', '') + (SELECT Util.dbo.StringConcat(kk.DropCons, @BatchTerminator) + @BatchTerminator
FROM (SELECT DISTINCT
CASE WHEN @IncludeIfExists = 1 THEN 'IF OBJECT_ID(''' + RingSchema + '.' + ForeignKey + ''') IS NOT NULL
' ELSE ''
END + 'ALTER TABLE ' + RingSchema + '.' + RingTable + ' DROP CONSTRAINT ' + ForeignKey AS DropCons
FROM fkey
WHERE IsRing = 1) kk)
IF @PrintSQL = 1
BEGIN
PRINT '--#region Drop Referenced Script'
EXEC Util.dbo.PrintLargeText
@DropRed
PRINT '--#endregion'
PRINT ''
PRINT '--#region Create Referenced Script'
EXEC Util.dbo.PrintLargeText
@CreateRed
PRINT '--#endregion'
PRINT ''
PRINT '--#region Drop Referencing Script'
EXEC Util.dbo.PrintLargeText
@DropRing
PRINT '--#endregion'
PRINT ''
PRINT '--#region Create Referencing Script'
EXEC Util.dbo.PrintLargeText
@CreateRing
PRINT '--#endregion'
END
GO
EXEC sys.sp_ms_marksystemobject sp_FK_script
GO