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:
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:
- Create new diagram in Diagrams folder under database
- Click Add table... and choose your table
- 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:
Bonus 2: Dataedo
You can also use tool called Dataedo to import your schema and view relationships in UI or HTML as shown below:
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.