How to view table foreign keys (FK) in SQL Server with SSMS (4 ways)

Piotr Kononow - Dataedo Team Piotr Kononow 2018-07-07

Table of Contents:


    SQL Server Management Studio is decent tool but far from perfect if you want to discover database schema. Here I want to show you a few ways to find table foreign keys.

    Option 1: View table columns

    In SSMS you can see which columns are foreign keys in Columns list under the table in Object Explorer. FKs have a gray/light key icon (depending on SSMS version).

    This option doesn't enable you to see the table FK references.

    Option 2: View table keys

    You can also see the list of foreign keys in Keys folder under the table.

    With this option, if the naming is right, you see referenced table but usually not which column has a foreign key.

    Names of FKs

    SSMS by default names new foreign keys in following way:

    • FK_Mytable_Othertable

    where Mytable is a table that holds foreign key and Othertable is the primary key table.

    Note: This is not always true though. DBAs/architects or other tools can name keys differently

    Option 3: Designer

    You can see all the details of foreign key in table designer.

    Please be careful not to change anything and save. You can open key definition in one of two ways:

    Option 1: select key → right click → Modify

    Option 1: select table → right click → Design → Relationships icon in toolbar or right click and choose Relationships → select key in Selected Relationship list on the left

    When you select the right key on the left expand Tables And Columns Specification option in the right panel. There are 4 fields defining foreign key - foreign and primary key tables and columns.

    Option 4: Diagrams

    Another way to see table foreign keys is to create diagram, add table and their related tables. Links will show you existing foreign keys.

    See complete tutorial on diagrams in SSMS or use this simplified guide:

    1. Create new diagram in Diagrams folder under database
    2. Click Add table... and choose your table
    3. Right click table and chose Add Related Tables

    Arrange diagram and relationships will be visible as links from your table to the other tables.

    Bonus 1: SQL query

    You can also query system catalog with an SQL to list foreign keys. In our query database we have one that would return this:

    Get a query (and many more)

    Bonus 2: Dataedo

    You can also use tool called Dataedo to import your schema and view relationships in UI or HTML as shown below:

    View live sample

    Other benefits besides better view of FKs is

    • to be able to define table and view relationships where there are no FK constraints in the database,
    • describe each schema element,
    • store metadata in global repository and easily share with your team in HTML
    • and much more.

    Try it for free now

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept