SQL Server 2005 keeps its dictionary of database objects in system tables like sys.objects, sys.columns, sys.foreign_keys, and sys.foreign_key_columns (similar tables or views exist for SQL Server 2000, but in the dbo schema instead of sys).
Finding relations between tables is straight forward by querying sys.foreign_keys and joining with sys.objects.
To list many-to-many relations within a database, we have to first be aware that such a relation is implemented by 3 tables and 2 foreign keys. If the relation has attributes, then the table that represents the relation has more than two columns; if the relation is a typed relation, it has an additional foreign key constraint. So keep in mind that we can only query the implementation, but not the semantics of the implementation.
We can then define a set of constraints to rule out unlikely candidates for many-to-many relations: in my query, I chose the simplest constraint that the foreign key columns must not be nullable.
The following SELECT statement starts with a table, its columns, distinct foreign key constraints, and joins with two distinct (supposed) master tables:
SELECT Master1.name AS Master1, Master2.name AS Master2, Detail.name AS Detail, DCol1.name AS Column1, DCol2.name AS Column2, ColumnCount.ColumnCount FROM sys.objects AS Detail INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id INNER JOIN sys.foreign_key_columns AS FKC1 ON DCol1.column_id = FKC1.parent_column_id AND DCol1.object_id = FKC1.parent_object_id INNER JOIN sys.objects AS Master1 ON FKC1.referenced_object_id = Master1.object_id INNER JOIN sys.columns AS DCol2 ON Detail.object_id = DCol2.object_id AND DCol2.column_id <> DCol1.column_id INNER JOIN sys.foreign_key_columns AS FKC2 ON DCol2.object_id = FKC2.parent_object_id AND DCol2.column_id = FKC2.parent_column_id INNER JOIN sys.objects AS Master2 ON Master2.object_id = FKC2.referenced_object_id INNER JOIN (SELECT object_id, COUNT(*) AS ColumnCount FROM sys.columns AS ColCount GROUP BY object_id) AS ColumnCount ON Detail.object_id = ColumnCount.object_id WHERE (DCol1.is_nullable = 0) AND (DCol2.is_nullable = 0) ORDER BY Master1, Detail, Master2