Listing Many-to-Many Relations in SQL Server 2005

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 AS Master1, AS Master2, AS Detail, AS Column1, AS Column2,
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
    (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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: