I am a huge fan of CodeSmith. If you are not familiar with it, and you do any amount of coding at all, you owe it to yourself to check it out.
Below is some template code which loads the foreign keys of a table into a Dictionary object. It stores the name of the table and foreign key field name. As you can see, it generates a notice when it comes across a recursive relationship.
public Dictionary<string,string> GetForeignKeyTables()
{
Dictionary<string,string> dic = new Dictionary<string,string>( );
for (int idx = 0; idx < SourceTable.ForeignKeys.Count; idx ++)
{
string primaryTable = String.Empty;
string primaryKey = String.Empty;
// To get the foreign key columns
ColumnSchema col1 = SourceTable.ForeignKeys[idx].ForeignKeyMemberColumns[0];
primaryKey = col1.Name.Trim();
// To get the foreign key tables
primaryTable = SourceTable.ForeignKeys[idx].PrimaryKeyTable.ToString().Replace("dbo.",String.Empty).Trim();
if (!dic.ContainsKey(primaryKey))
dic.Add(primaryKey, primaryTable);
else
MessageBox.Show("This table may contain recursive relationships. You must set those relationships up manually.");
}
return dic;
}