3

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.

evictednoise
  • 587
  • 1
  • 7
  • 19
  • If you have SSMS installed you can expand upon the database and have a look (or create) a database diagram. Yet, such relations are only shown if they have been setup that way. Possibly your diagram is empty. – Ralph Mar 15 '16 at 16:49
  • 1
    The SSMS database diagramming can't be used in my case as I wrote in the question. But I have all foreign keys in place. – evictednoise Mar 15 '16 at 17:09
  • Since you seemingly cannot use the database diagrams you will certainly not get a "diagram" as you asked for. With SSMS you can merely send a query and get (in return) the answer in form of one or multiple tables. So, you need to rewrite your question and explain "how you would want to see the relationship between two tables in a table". – Ralph Mar 18 '16 at 19:27

1 Answers1

0

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.

Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42
  • 1
    Thanks for the tip about the relationships. As for the code, how would you suggest using it to get the expected answer? How do I get the dependant table chain with this query? – evictednoise Mar 18 '16 at 06:54
  • That depends on your flavor. The above table gives you all the references. Creating a visually appealing "chain" in a table is not easy. But there are many examples for "parent child hierarchies" including their path. Just dump the above result into a table variable and then expand up it to your taste with any of the following: http://stackoverflow.com/questions/15282938/dump-list-of-full-paths-of-parent-child-type-records-in-sql-server-2008-r2 or http://stackoverflow.com/questions/14241936/how-can-i-generate-a-hierarchy-path-in-sql-that-leads-to-a-given-node or google for more. – Ralph Mar 18 '16 at 15:23
  • Thanks Ralph, I will write something in SQL if I won't find another way. – evictednoise Mar 21 '16 at 10:34