Suppose we have the following tables, with pretty foreign keys in place.

CREATE TABLE Person
(
    Id int not null
    --other stuff
)
CREATE TABLE Employee
(
    Id int not null,
    PersonId int not null,
    UserId int null
    --other stuff
)

CREATE TABLE User
(
    Id int not null,
    Name varchar(25) not null,
    Password varchar(25) not null
    --other stuff
)

CREATE TABLE Roles
(
    Id int not null
    --other stuff
)

CREATE TABLE UserRoles
(
    UserId int not null,
    RoleId int not null
    --other stuff
)

What are the ways(query, additional software?) to ask
"What is the relationship between table X and Y?"

E.g. I would like to 'ask' :
What is the relationship between tables Person and Roles?

Expected answer :

Person 1:N Employee 1:1 User 1:N UserRoles N:1 Roles

Note that tables Person and Roles do not have a direct relationship. The expected result should list the tables in-between of these two.

Something like this. A diagram representation would do, but it should only have the tables involved in the relationship.

Why I can't use "Database Diagrams" in SSMS.

Creating a relevant diagram with only needed tables takes too much time looking up the references by hand.
I can't use "Add Related tables" because it makes the diagram absolutely unreadable by adding 200+ tables.

The difference from diagramming would be that I only want to input two table names.