Database Diagrams with SSMS - Too many tables / do changes to the diagram modify the database structure?

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  October 9, 2018 at 2:12 pm

  #378564

  We have SQL Server 2012 SP3. I am considering creating a Database Diagram. We have a vendor application with over 3,500 tables. I am trying this in a non-production environment first. In the Add Table Dialog, after selecting all of the tables to include in the Diagram and clicking Add, the 'Stop responding...' message is displayed. I clicked the X in the top right corner to exit. Then I received the cancel or wait it out options. I clicked cancel.

  1. Is 3,500 tables too many for SQL Server Diagramming Tool? Or, should I select all of the tables and just wait it out?

  2. Once a database diagram is created, if changes are made to this same diagram, for example delete a table or change a column, would these change be implemented in the underlying database structure? (I have heard/read this somewhere and just want to be careful not to make any accidental changes.)

  Thanks in advance.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 9, 2018 at 3:07 pm

  #2008746

  HookSqlDba7 - Tuesday, October 9, 2018 2:12 PM

  We have SQL Server 2012 SP3. I am considering creating a Database Diagram. We have a vendor application with over 3,500 tables. I am trying this in a non-production environment first. In the Add Table Dialog, after selecting all of the tables to include in the Diagram and clicking Add, the 'Stop responding...' message is displayed. I clicked the X in the top right corner to exit. Then I received the cancel or wait it out options. I clicked cancel.

  1. Is 3,500 tables too many for SQL Server Diagramming Tool? Or, should I select all of the tables and just wait it out?

  2. Once a database diagram is created, if changes are made to this same diagram, for example delete a table or change a column, would these change be implemented in the underlying database structure? (I have heard/read this somewhere and just want to be careful not to make any accidental changes.)

  Thanks in advance.

  I wish I could tell you, but... I don't have any one DB with 3500 tables.  Is it not possible to subset the tables into multiple diagrams?  Even visio and other professional diagramming tools have a hard time with laying things out with 1000 tables.  Even if it does render - not sure how you'd find anything on it.

  As to your second question - yes - if you delete tables from the diagram (as opposed to removing it from the diagram), it will drop the table.  if you remove a column then sane, it will drop the column from the table.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?