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.
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.