CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Antony Prince Peter
Antony Prince Peter

Reputation: 21

Select query to find the constraint which has PK/FK in one table to list all the related tables in SQL Server

Could you help to get the select query to list all the related tables to the PK/FK of another table)

I have 3 tables, Table1 contains cust_ID (PK) and other 2 tables (table2 and table3) have a FK to Table1.

Need to use constraints for filter all the related tables and list in the output specifically list the Reference Table and Reference column and Reference Key from table2 and table3 - may be in production to list all the relatable tables and columns (in millions)

Unable to fetch the 2 columns Reference Table and Reference column and Reference Key(complete list).

Could you help to list all the foreign key tables and columns based on the other primary key from another table

The primary key (in table1) should list the foreign keys (in Table2 and Table3) with their table and column name.

Table1

cust_ID cust_Name
10001 John
10002 Peter
10003 Steve
10004 Mike
10005 Rob

Table2

cust_ID_bk cust_Pay
10001 10
10002 20

Table3

cust_ID_sk cust_Phone#
10001 1234567890
10002 2023456789
10003 5678904345

Expected Output

CONSTRAINT_Name CONSTRAINT_Type TABLE_NAME COLUMN NAME Reference Table Reference Column Reference Key
SYS_12345 Primary Key Table1 cust_ID Table2 cust_ID_bk Foreign Key
SYS_12345 Primary Key Table1 cust_ID Table3 cust_ID_sk Foreign Key

I tried using below query but I am unable to fetch the Reference Table and Reference column and Reference Key and Reference Key

select 
    a.CONSTRAINT_NAME, a.CONSTRAINT_TYPE, a.TABLE_NAME,
    b.COLUMN_NAME, b.Reference Table, b.Reference Column, b.Reference Key
from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS a, 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE b
where 
    a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
    and a.table_name = 'Table1'

Upvotes: 1

Views: 842

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

create table Table1(cust_ID int,
                    cust_Name varchar(50),
                    CONSTRAINT [SYS_12345] Primary key  (cust_id )
                   );

create table Table2(cust_ID_bk int, 
                    cust_Pay varchar(50),
                    Foreign key (cust_ID_bk) references Table1(Cust_ID)
                  );

create table Table3(cust_ID_sk int, 
                   cust_Phone# varchar(50),
                   Foreign key (cust_ID_sk) references Table1(Cust_ID)
                 );

Query:

select 
    a.CONSTRAINT_NAME, a.CONSTRAINT_TYPE, a.TABLE_NAME,
    b.COLUMN_NAME, object_name(fkc.parent_object_id) Reference_Table,
    col.name Reference_Column ,'Foreign Key' as ReferenceKey
from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
inner join 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE b on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
left join 
    sys.foreign_key_columns fkc on a.TABLE_NAME =  object_name(fkc.referenced_object_id) 
left join
    sys.columns AS col ON fkc.parent_object_id = col.object_id 
                       AND fkc.parent_column_id = col.column_id
where  
    a.table_name = 'Table1' and a.CONSTRAINT_TYPE='PRIMARY KEY'

OUtput:

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME COLUMN_NAME Reference_Table Reference_Column ReferenceKey
SYS_12345 PRIMARY KEY Table1 cust_ID Table2 cust_ID_bk Foreign Key
SYS_12345 PRIMARY KEY Table1 cust_ID Table3 cust_ID_sk Foreign Key

fiddle

Upvotes: 0

Related Questions