Question:

Identify foreign key references OR Referenced Tables and its key(s) for a Parent table in SQL Server.

Script:

;WITH CTE AS
(

	SELECT OBJECT_NAME(PARENT_OBJECT_ID) CHILDTABLE, TYPE,NAME,OBJECT_NAME(REFERENCED_OBJECT_ID) PARENTTABLE,
	DELETE_REFERENTIAL_ACTION_DESC, UPDATE_REFERENTIAL_ACTION_DESC FROM SYS.FOREIGN_KEYS
	
	--SEARCH TABLENAME (PARENT TABLE)
	WHERE OBJECT_NAME(REFERENCED_OBJECT_ID) = 'ParentTable Name'

	UNION ALL

	SELECT OBJECT_NAME(PARENT_OBJECT_ID), A.TYPE,A.NAME,OBJECT_NAME(REFERENCED_OBJECT_ID),
	A.DELETE_REFERENTIAL_ACTION_DESC, A.UPDATE_REFERENTIAL_ACTION_DESC FROM SYS.FOREIGN_KEYS A
	INNER JOIN CTE B ON OBJECT_NAME(A.REFERENCED_OBJECT_ID) = B.CHILDTABLE
	WHERE PARENTTABLE !=  CHILDTABLE
	
)
,CTE1 AS
(
	SELECT DISTINCT A.*,B.COLUMN_NAME  FROM CTE A
	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
		ON A.NAME = B.CONSTRAINT_NAME
)
SELECT 
	DISTINCT CHILDTABLE,PARENTTABLE,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE1 A
					WHERE A.NAME = B.NAME AND A.CHILDTABLE = B.CHILDTABLE AND A.PARENTTABLE = B.PARENTTABLE
					FOR XML PATH ('') )
					, ' ', ',') 
FROM CTE1 B OPTION(MAXRECURSION 32767)