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.
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.
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.
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
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.
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:
Arrange diagram and relationships will be visible as links from your table to the other tables.
You can also query system catalog with an SQL to list foreign keys. In our query database we have one that would return this:
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