How to Find Table Which have FOREIGN key Referenced

Hi Frnds ,
I think my blog title will not properly specify what i am writing in this tutorial.
Let me explain the scenario.
For Example You have a order table with primary key OrderId
and this OrderId refered to many tables in database as FOREIGN Key. Now if you want to create a query in which you pass OrderId and want to get Table Name where exist this orderid. Here I am writing the solution.

Create PROCEDURE [dbo].[GetOrderDetails]
@orderId bigint
AS
DECLARE @tablename VARCHAR(50)
, @value VARCHAR(50)
, @query NVARCHAR(MAX)
,@OutPutTable varchar(50)
SET @tablename = ''
SET @value = CONVERT(VARCHAR(50), @orderId)
BEGIN
SELECT @query = STUFF(
(
SELECT ' UNION ' + 'SELECT DISTINCT ''' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ''' as ColName, ''' + OBJECT_NAME(f.parent_object_id) + ''' as TblName'
+ ' FROM ' + OBJECT_NAME(f.parent_object_id)
+ ' WHERE ' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ' = ''' + @value + ''''
FROM SYS.FOREIGN_KEYS AS f
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = @tablename
FOR XML PATH('')
), 1,6,'')
create table #temp (ColName varchar(100),TblName varchar(100))
insert into #temp EXEC (@query)
set @OutPutTable=(select TblName from #temp)
--select *,@OutPutTable as 'A1' from #temp
drop table #temp
END

I am explaining it with the example

My Primary Key Table

Order
OrderId	Name	ServiceName
1	a	Book
2	b	Music
3	c	Video
4	d	Book

BookOrders
BookId	Name	OrderId
1	Book1	1
1	Book1	4

MusicOrders
MusicId	Name	OrderId
1	Music1	2

VideoOrders
VideoId	Name	OrderId
1	Video1	3

Now If You Want to pass OrderId and get FK Table where OrderId Exists. then Use above Procedure . If you pass 3 then result will VideoOrders. if 4 then result will BookOrders

Thanks
Please Do Comments if you like / dislike