How to programmatically find a many to many relation in an SQL Server 2005 database

Question

I can find a direct table foreign key in c# like:

  foreach (ForeignKey key in CurrentTable.ForeignKeys) {
                    Console.WriteLine("FK:" + key.ReferencedTable);
          }

The classic representation for a many to many relationship is achieved via an intermediate table that has a one to many relation to the two principal tables so there is not a direct link between the tables that have the many2many relation.

For the moment I try to find the many to many relation checking if the table I am analyzing has only two columns and both colums are foreign keys to different tables (in that case I recognise this as the intermediate table).

This is not the best solution as sometimes the intermediate table carries also other columns.

What would the best way to find this many2many relation programmatically?

Answer 1

As additional criteria you could use the fact that in many cases the couple of the two foreign keys represents the primary key of the join/intermediate table.

Answer 2

You seem to have posited your own solution. Rather than searching for tables that have two foreign keys referencing other distinct tables and with no other columns, just delete the phrase "and with no other columns".

In other words, look for "tables that have two foreign keys referencing other distinct tables".

Answer 3

This statement finds all tables with at least two foreign key columns

SELECT  objects_1.name AS Master1, objects_2.name AS Master2, sys.objects.name AS Detail, 
    sys.columns.name AS Column1, columns_1.name AS Column2
FROM sys.objects 
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id 
INNER JOIN sys.foreign_key_columns ON sys.columns.column_id = sys.foreign_key_columns.parent_column_id 
    AND sys.columns.object_id = sys.foreign_key_columns.parent_object_id 
INNER JOIN sys.objects AS objects_1 ON sys.foreign_key_columns.referenced_object_id = objects_1.object_id 
INNER JOIN sys.columns AS columns_1 ON sys.objects.object_id = columns_1.object_id 
    AND columns_1.column_id <> sys.columns.column_id 
INNER JOIN sys.foreign_key_columns AS foreign_key_columns_1 ON columns_1.object_id = foreign_key_columns_1.parent_object_id 
    AND columns_1.column_id = foreign_key_columns_1.parent_column_id 
INNER JOIN sys.objects AS objects_2 ON objects_2.object_id = foreign_key_columns_1.referenced_object_id
WHERE     (sys.columns.is_nullable = 0) AND (columns_1.is_nullable = 0)
ORDER BY Master1, Detail, Master2