1

I run Microsoft SQL Server Express 64-bit version 14.0.2027.2 (X64) on Windows 10 Pro 10.

I created a linked server connecting tables from a Microsoft Access (mdb) database to Server Objects/Linked Servers on my SQL Server Express instance. All the linked tables seem to be working well.

I would like to create a Database Diagram for the linked tables. Since I am new to SQL Server, as far as I know ER diagram is possible to make only for the local database.

Is there any option how to make an ER diagram for linked server tables?

If not (when diagram is allowed only for native database/tables), is there any possibility to link tables directly into a native SQL Server database and then create diagram for tables?

2

There's no way to do this with the native tools within SQL Server Management Studio. You could get a third party tool like ER/Studio, but even that won't automatically model the linked tables. You'll have to enter them manually. External tables just aren't considered to be a part of the fundamental model of a given database. They're external, additional, other.

2
  • Does it mean that I can use full functionality of MSSQL for linked tables in third party tool like ER/Studio? My intention is: - to have SQL database for linked data from Access mdb (since data should stay in Access) - to create views with linked tables, use user defined functions, create diagram for linked tables. As you answered, I am not able to make ER diagram for linked tables in MSSQL Management Studio. If I will use suggested third party tool, would it help me to overcome this limitation and meet my expectations (creating views, functions for linked tables)?
    – radimmachu
    Mar 27 '20 at 12:37
  • If you're just looking for documentation, yes, a third party tool could do it. I'm positive that the third party tools won't do it automatically though. You'll have to enter all the table structures manually. Mar 28 '20 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.