Find relationship between any table A and B in Sql Server

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.

Answers


I believe this might be what you are looking for:

select  t.name as TableWithForeignKey,
        c.name as ForeignKeyColumn,
        t2.name as DependentOnTable,
        c2.name as ReferencedColumn,
        N'N:1'
from    sys.foreign_key_columns as fk
inner join sys.tables as t
on      fk.parent_object_id = t.object_id
inner join sys.columns as c
on      fk.parent_object_id = c.object_id
        and fk.parent_column_id = c.column_id
inner join sys.columns as c2
on      c2.object_id = fk.referenced_object_id
        and c2.column_id = fk.referenced_column_id
inner join sys.tables as t2
on      t2.object_id = c2.object_id
order by TableWithForeignKey

Note that all relationships in SQL server are 1:N because

neither a 1:1 can be established: How do I create a real one-to-one relationship in SQL Server

nor a N:N relationship can be established: Foreign Key to non-primary key

If you want to setup such relationships then you can merely use the extended properties to write it down for yourself and then "manually" enforce it.


Need Your Help

Calculate reachability for every vertex from adjacency list

algorithm graph graph-algorithm floyd-warshall transitive-closure

Given an adjacency list Map<Vertex, Set<Vertex>> for a DAG, I want to calculate the reachability of every vertex (i.e. if there there is path from u to v).

After upgrading to Rails 4 Ckeditor gives undefined method `get!'

ruby-on-rails ruby ruby-on-rails-4 ckeditor

After upgrading to Rails 4, the following code broke the site giving the error undefined methodget!'`