How to get all the foreign key table referenced with Master table

Polachan Paily 91 Reputation points
2022-06-16T06:41:46.757+00:00

I have a Product table as parent table . How can I get all the related tables with Product with foreing key reference . Please can you help

Developer technologies | Transact-SQL
{count} votes

2 answers

Sort by: Most helpful
  1. Bert Zhou-msft 3,436 Reputation points
    2022-06-16T06:49:59.277+00:00

    Hi,@Polachan Paily

    Try the following code:

    select  t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo, c.name as ForeignKeyColumn   
    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  
    where  fk.referenced_object_id = (select object_id    
                                                          from sys.tables   
                                                          where name = ' Product')----add your tablename  
    
    order by TableWithForeignKey, FK_PartNo  
    

    IF you want to get useful information by simple code,you could try it:

    EXEC sp_fkeys ' Product'  
    

    Best regards,
    Bert Zhou

    0 comments No comments

  2. Olaf Helper 47,516 Reputation points
    2022-06-16T07:25:51.883+00:00

    In SSMS rigth-mouse click on the table => Show dependencies.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.