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