USE master
GO
IF OBJECT_ID('sp_FK') IS NULL EXEC ('CREATE PROCEDURE sp_FK AS SELECT 1 AS ID')
GO
ALTER PROCEDURE sp_FK
@ObjectName SYSNAME,
@ShowParent BIT = 1,
@ShowReferenced BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_FK',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT 'GO
'
DECLARE @OBJECTID INT = OBJECT_ID(LTRIM(RTRIM(@ObjectName)))
IF @OBJECTID IS NULL
BEGIN
PRINT 'TABLE ' + @ObjectName + ' NOT FOUND IN THE DATABASE ' + DB_NAME() ;
RETURN
END ;
WITH AllTables
AS (SELECT s.SCHEMA_ID,
s.NAME AS SchemaName,
o.OBJECT_ID,
o.NAME AS ObjectName,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS TYPE,
QUOTENAME(s.NAME) + '.' + QUOTENAME(o.NAME) AS FQN
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE o.is_ms_shipped = 0),
FK1
AS (SELECT pt.FQN AS RingFQN,
pt.SchemaName AS RingSchema,
pt.ObjectName AS RingTable,
QUOTENAME(pt.SchemaName) + '.' + QUOTENAME(fk.NAME) AS FKFQN,
fk.NAME AS FKName,
rt.FQN AS RedFQN,
rt.SchemaName AS RedSchema,
rt.ObjectName AS RedTable,
'f' + CAST(DENSE_RANK() OVER (PARTITION BY fk.parent_object_id ORDER BY rt.SchemaName, rt.ObjectName, fk.NAME) AS VARCHAR) AS ALIAS,
fk.is_disabled AS IsDisabled,
fk.is_not_trusted AS IsNotTrusted,
fk.is_system_named AS IsSystemNamed,
fk.is_not_for_replication AS IsNotForReplication,
delete_referential_action AS DeleteAction,
delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DeleteActionDesc,
update_referential_action AS UpdateAction,
update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS UpdateActionDesc,
fk.create_date AS CreateDate,
fk.modify_date AS ModifyDate,
fk.OBJECT_ID AS FKObjectId,
pt.OBJECT_ID AS RingObjectId,
rt.OBJECT_ID AS RedObjectId
FROM sys.foreign_keys fk (NOLOCK)
INNER JOIN AllTables pt ON fk.parent_object_id = pt.OBJECT_ID
INNER JOIN AllTables rt ON fk.referenced_object_id = rt.OBJECT_ID
WHERE ((@ShowParent = 1
AND pt.OBJECT_ID = @OBJECTID)
OR (@ShowReferenced = 1
AND rt.OBJECT_ID = @OBJECTID)))
SELECT CASE WHEN RedObjectId = @OBJECTID THEN 1
ELSE 0
END AS Referenced,
CASE WHEN RingObjectId = @OBJECTID THEN 1
ELSE 0
END AS REFERENCING,
RingFQN,
RingSchema,
RingTable,
RingColumns,
FKFQN,
FKName,
ColumnCnt,
RedFQN,
RedSchema,
RedTable,
RedColumns,
IsDisabled,
IsNotTrusted,
IsSystemNamed,
IsNotForReplication,
DeleteAction,
DeleteActionDesc,
UpdateAction,
UpdateActionDesc,
CreateDate,
ModifyDate,
FKObjectId,
RingObjectId,
RedObjectId,
'LEFT OUTER JOIN ' + QUOTENAME(RedSchema) + '.' + QUOTENAME(RedTable) + ' ' + ALIAS + ' ON ' + REPLACE(JoinScript, '#Alias#', ALIAS) AS JoinScript,
CreateScript,
DropScript
FROM FK1
CROSS APPLY (SELECT ColumnCnt,
RingColumns,
RedColumns,
JoinScript,
'IF OBJECT_ID(''' + FKFQN + ''') IS NOT NULL ALTER TABLE ' + RingFQN + ' DROP CONSTRAINT ' + QUOTENAME(FKName) AS DropScript,
'ALTER TABLE ' + RingFQN + CASE WHEN IsDisabled = 1 THEN ' WITH NOCHECK'
ELSE ''
END + ' ADD CONSTRAINT ' + QUOTENAME(FKName) + ' FOREIGN KEY (' + RingColumns + ') REFERENCES ' + RedFQN
+ ' (' + RedColumns + ')' + CASE WHEN DeleteAction > 0 THEN ' ON DELETE ' + DeleteActionDesc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''
END + CASE WHEN UpdateAction > 0 THEN ' ON UPDATE ' + UpdateActionDesc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''
END + CASE WHEN IsNotForReplication = 1 THEN ' NOT FOR REPLICATION'
ELSE ''
END + CASE WHEN IsDisabled = 1 THEN '
ALTER TABLE ' + RingFQN + ' NOCHECK CONSTRAINT ' + QUOTENAME(FKName) + '
' ELSE ''
END AS CreateScript
FROM (SELECT COUNT(*) AS ColumnCnt,
Util.dbo.StringConcat(QUOTENAME(RingColumn), ', ') AS RingColumns,
Util.dbo.StringConcat(QUOTENAME(RedColumn), ', ') AS RedColumns,
+Util.dbo.StringConcat('pr.' + QUOTENAME(RingColumn) + ' = ' + '#Alias#' + '.' + QUOTENAME(RedColumn), ' AND ') AS JoinScript
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 = RingObjectId
AND fc.parent_column_id = pc.column_id
INNER JOIN sys.columns rc (NOLOCK) ON rc.OBJECT_ID = RedObjectId
AND fc.referenced_column_id = rc.column_id
WHERE fc.constraint_object_id = FKObjectId
ORDER BY fc.constraint_column_id) k) fc) k
ORDER BY Referenced DESC,
REFERENCING DESC,
RingFQN,
RedFQN
GO
EXEC sys.sp_ms_marksystemobject sp_FK
GO