2

I am trying to export 3 database (DB) diagrams from one DB to another using SSMS 17. I have tried the solution suggested here:

How to export a SQL Server 2008 Database Diagram to another DB?

This solution is also recommended in several other websites I have found so far, to copy the content of the table "sysdiagrams" from the source DB to the destination DB (3 rows in my case).

After doing this I can confirm that both "sysdiagrams" tables have the same content and the diagrams are imported in the destination DB. Nevertheless in each imported diagram I see only the tables but not the relationships. What I find strange is that the relationships are shortly visible while the tables in the diagram are being loaded. After all tables are loaded they just dissappear.

Does anyone know what the issue could be? It is my understanding, that this solution is successful for early versions of SSMS.

Any help is highly appreciated :) !

Diego Ballén
  • 23
  • 1
  • 3
  • Check that the relationships that you think exist in the target database, are really there. The diagram editor will behave as you describe when the diagram has "lines" in it; the editor will look for the relationship of each "line" and it will remove "lines" for which verification fails (after drawing them initially). – Peter B Mar 05 '19 at 11:33
  • Thank you for your answer PeterB! – Diego Ballén Mar 05 '19 at 12:14
  • When I say that the relationships are missing, I mean precisely that the lines connecting the tables shortly appear and then dissappear. In the source DB I created the relationships using the lines (no queries used). They appear all the time in the source diagrams. As far as I understand all information of the diagram is contained in the table "sysdiagrams". May be there is some other data that must be updated? What could cause the verification to fail? – Diego Ballén Mar 05 '19 at 12:26
  • I'm inclined to say that the relationships are missing. You **can** create a relation between two tables using the diagram editor: it will create a **relation** between the tables, and it will put a **line** in the diagram. With your diagram copy action, you only copy all the **lines** but not the actual **relations**. So the lines disappear when you view the copied diagram in the editor, because the relations are " the truth" of what is in your database. You need to write ALTER TABLE ... ADD CONSTRAINT ... to re-create the relations, or try using a database diff tool. – Peter B Mar 05 '19 at 12:33
  • Hi Peter B, I confirmed that the relationships do not exist in the target DB. So, you are right. The diagram has no lines since there is no relationships to visualize. Could you create an answer to mark it as "answer"? – Diego Ballén Mar 06 '19 at 08:31

1 Answers1

0

Check that the relations (Foreign Key constraints) actually exist in the target database, because if they don't then the diagram editor will behave exactly as you describe: initially it will draw all lines, then it starts looking for each FK relation that each line represents, and it will one by one remove lines for which relations are not found.

To clarify, you can create FK relations using the Diagram Editor by drawing a line between to tables (the editor then creates both a line in the diagram and the actual FK constraint). But with a 'copy diagram' action like yours, you copy only the line data but not the actual relations.

To add the missing relations in the target database you could write ALTER TABLE ... ADD CONSTRAINT ... statements, or use the Diagram Editor again on the target database, or you can try using a database diff tool.

Peter B
  • 22,460
  • 5
  • 32
  • 69