In replication, we need to add objects as articles to replicate the data to different server.The important point is here that if we have any reference(foreign keys) associated with the articles(tables choosen to be replicated), then we need to consider the parant table also to add as article, Or the replication might fail due to the foreign key enforcement at subscriber.

Few days back, one of my collegue DBA asked me to script out to find the related tables of a table that to be added as article as part of replication set up. So that he can add those tables as articles to prevent the breaking of foreign key enforcement in replication.

Here, I would like to share the script that I used.

Declare @Table_Name Varchar(50)
Set @Table_Name = 'table_name'
Create Table #Temp_Tables (Row int identity(1,1),To_Referenced Varchar(100), TableName Varchar(100),Evaluated Int)

Insert Into #Temp_Tables Select @Table_Name,NULL,0

Declare @TName Varchar(100),@RCount int,@Inc Int
Set @Inc  = 0
Select @RCount= COUNT(1) From #Temp_Tables Where Evaluated =0
While @RCount>0
Begin
    Set @TName =  (Select  Top 1 To_Referenced From #Temp_Tables Where Evaluated = 0)
	      
	Insert Into #Temp_Tables
	Select OBJECT_NAME(referenced_object_id),OBJECT_NAME(Parent_Object_Id),0 From sys.foreign_key_columns Where OBJECT_NAME(Parent_Object_Id)=@TName

	Update #Temp_Tables Set Evaluated=1 Where To_Referenced = @TNAme 

	Select @RCount= COUNT(1) From #Temp_Tables Where Evaluated =0

End


Select * From #Temp_Tables Order by Row asc


Drop Table #Temp_Tables

As always, if you have any better idea/method or nay comments, I request you to share.