Find table relations from SQL Server

Question

I would like to know how to discover which tables are related each other in my SQL Server database by running a SQL query.

As example, suppose I have 2 many-to-many relations and 3 tables initialized with some random values.

create table A (pk int not null primary key identity, A int);
create table B (pk int not null primary key identity, B int);
create table C (pk int not null primary key identity, C int);

create table AB 
(
    a_pk int not null references A,
    b_pk int not null references B,
    primary key(a_pk, b_pk)
);

create table AC 
(
    a_pk int not null references A,
    c_pk int not null references C,
    primary key(a_pk, c_pk)
);

I would like to run a parametrized query having as output something like:

With parameter 'A':

MM_TAB  |  REL_TAB
------------------
  AB    |    B 
  AC    |    C

With parameter 'B':

MM_TAB  |  REL_TAB
------------------
  AB    |    A

With parameter 'C':

MM_TAB  |  REL_TAB
------------------
  AC    |    A

How can I do that?

check out this post: https://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema — Nate Anderson, Jul 11 '17 at 19:31

Ronaldo Cano · Accepted Answer · 2017-07-12T16:16:18.743

Check this out:

I created the tables and relations using SQL Server 2014:

create database relation

use relation

create table A (pk int not null primary key identity, A int);
create table B (pk int not null primary key identity, B int);
create table C (pk int not null primary key identity, C int);

create table AB 
(
    a_pk int not null  foreign key references A(pk),
    b_pk int not null foreign key references B(pk),
    primary key(a_pk, b_pk)
);

create table AC 
(
    a_pk int not null  foreign key references A(pk),
    c_pk int not null foreign key references C(pk),
    primary key(a_pk, c_pk)
);

Then you can use this query to find the related tables:

    use relation 
go

DECLARE @table_param NVARCHAR(2)='A';

with rel as(
        SELECT 
            K_Table = FK.TABLE_NAME,
            --FK_Column = CU.COLUMN_NAME,
            PK_Table = PK.TABLE_NAME
            --PK_Column = PT.COLUMN_NAME,
            --Constraint_Name = C.CONSTRAINT_NAME
        FROM 
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
        INNER JOIN 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
        INNER JOIN 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
        INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
        wHERE PK.TABLE_NAME = @table_param

)

SELECT 
    K_Table = FK.TABLE_NAME,
    --FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME
    --PK_Column = PT.COLUMN_NAME,
    --Constraint_Name = C.CONSTRAINT_NAME
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE FK.TABLE_NAME IN(SELECT R.K_Table FROM REL R)

Your query works nice, but no matter the `@table_param` I specify, it always returns ALL the relations in my db, while I only need the small subset I specified in my question. Any suggestion about how to improve it? — jjhorn, Jul 12 '17 at 14:30
you are rigth, the filter was not working correctly, i fixed it, please take a look — Ronaldo Cano, Jul 12 '17 at 15:18
there's still something wrong, since with `@table_param = 'A'` it returns `AB | A` and `AC | A` instead of `AB | B` and `AC | C`. In other words, `K_Table` is correct, but in `PK_Table` I need the other table involved in the relation (not `@table_param`) — jjhorn, Jul 12 '17 at 15:43
using a CTE, you should be able to get all the related tables, hope it helps, please look at the last query — Ronaldo Cano, Jul 12 '17 at 16:16
By adding `AND PK.TABLE_NAME != @table_param` in the last part of the query it works perfectly! Thanks! Problem solved — jjhorn, Jul 12 '17 at 16:47

Find table relations from SQL Server

1 Answers1