How to make Database Diagram visually show Foreign Keys in Management Studio?

Question

I'm reviewing this database and creating the foreign keys, believe me they did not exist. I'm using SSMS to visually let me know what foreign keys are missing and also create them. Is there any configuration to let the diagram designer show if a specific column is already part of a foreign key? That would help me identify missing FKs faster.

This is how it's showed currently:

This is what I'm looking for (or something like that):

I just need some visual indicator (like I've seen in other tools) that shows me if a column is part of a FK.

Solution 1

In SSMS, if you expand the tree of tables for your database, then expand the Columns folder, the icon next to a column will be a silver/gray key if it's part of a foreign key relationship, but unfortunately it won't show you the column to which column it's mapped.

You could also create a new diagram in SSMS by right-clicking the "Database Diagrams" folder underneath your database in the tree and choosing "New Database Diagram." You will get lines between the tables where foreign keys exists.

You could also use third-party tools to reverse engineer a diagram from your DB schema, like Microsoft Visio or Sparx Enterprise Architect.

I'm not sure SSMS Express supports these things, so you might be out of luck with anything fancy/visual.

Solution 2

I know this is an old post but this may help others.

within SSMS Database Diagram by right-clicking a table > then select "table view" > then select "keys" this will only show PK and FK and all other keys in that table. OK you say you need it on all tables that's fine in within SSMS Database Diagram click the background (with no items selected) then press "Ctrl+A" to select all then with your mouse over a selected table right-clicking > then select "table view" > then select "keys" this will show PK and FK and all other keys in each selected tables. then if you need to see the full set of column names for a given table you can change the "table view" for that one and change it back.

This way you see what has and what has not got FK ...

Hope this helps others like it has me.

Solution 3

I have not found a way to do it visually in the Management Studio but you can try the following:

select f.name as ForeignKey, OBJECT_NAME(f.parent_object_id) as TableName,
       COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
       OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
       COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
  from sys.foreign_keys f
       inner join sys.foreign_key_columns fc ON f.OBJECT_ID = fc.constraint_object_id

this is not visual but you will be faster to check

Comments

[Cᴏʀʏ]

You can turn on relationship labels by right clicking in your diagram and selected "Show Relationship Labels." That's as close as you can get I think.

[Adriano Carneiro]

I've tried that. It's not really what I'm looking for, because it does not help me in building the missing FKs...

[Adriano Carneiro]

That shows me the FKs already created. The thing is I need to know the ones not created because I'm creating the FKs for this database. I have a good knowledge of this DB, so I'm looking for a way to see a visual indicator for the fields in the Diagram that shows whether a field is part of a FK or not.

[Cᴏʀʏ]

If you have Visio you can reverse engineer the database diagram. It will connect to anything you have drivers for and it will put an "FK" next to the columns that are foreign keys.