Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Angry Unanswered: How to view sysdiagrams

    This weekend did some research and confirmed that the answer is still not available, because it looks like nobody has asked a question: how to view the contents of a database diagram as it is stored in dbo.sysdiagrams of a user database? There are several places that show the most @$$-backward ways of how to transfer a diagram from 1 server to another, or how to script it using 4K chunks. But the most important question (how to view contents) hasn't come up.

    The reason for me to need this is simple, - I am working with 76000-table database and some vendor-supplied diagrams that span several pages. I am creating images from these diagrams, and then using ImageMap control in ASP.NET associating each table and column with their physical definition from system tables. So far I've been using a workaround (very clumsy and prone to inaccuracies) by starting a heavily filtered profiler trace (via script) that sends the output to a trace file, while opening a diagram in SSMS. Once the diagram is fully open (takes up to a couple of minutes if it's big), - I run a "stoptrace" script, followed by joining sys.tables with the output from ::fn_trace_gettable(<tracefile>,0). If nobody else is looking at other diagrams (there are 116 diagrams) in this database, then I am good and I correctly associate the diagram with all tables that go into it. Otherwise, - I'm toast and will end up associating tables from other diagrams.

    Ideal solution would be to read the varbinary data stored in the definition field of sysdiagrams table. But without knowing the algorythm used by MS I have no other idea how to solve this.

    And yes, I've run profiler back and forth while opening existing, creating and saving new, and adding and removing tables. A set of diagram-related procedures that pop up in the trace are all using @definition varbinary(max) as an input parameter, and nothing shows up where this varbinary value gets composed.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rdjabarov View Post
    I am working with 76000-table database
    Somebody is overcompensating for something....

    ...and I'm sure MikeBikeKite could show you how to reduce this to two or three tables using an EAV design.

    Otherwise, I'd guess you are toast. I'd be very surprised if there was any way to decode that data without running it through some MS tool embedded in the diagram utility. My only hope would have been that it was stored in XML, like about half of everything else MS does, but apparently not, eh?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    XML would be nice, and I suspect that the image in SSMS is actually in XML. But, as with text annotations in SSIS packages, gets converted on the fly to varbinary. Text Annotations mystery is partially solved here, but diagram definition is yet to come...

    And I have a much smaller database that I already completed, - 9648 tables. Only took 2.5 weeks
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    creating the imagemap for a 76000 table diagram by hand? you should get an intern to do that.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think he should just draw a bunch of random rectangles and lines in visio, because a 76000 table ERD is never going to be used anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2005
    Posts
    319

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by jezemine View Post
    creating the imagemap for a 76000 table diagram by hand? you should get an intern to do that.
    I didn't realize I had to mention that it is NEVER a good idea to put everything you have into 1 diagram, but hey, better late than never, huh?! (pun intended, of course)

    It is the notorious SAP database structure, and no, I don't want to have all their silly-named tables on 1 diagram. In fact, the table names are not even the originals. I discovered some "hidden" documentation that provides English-like naming, extracted it, put description into MS_DESCRIPTION extended property by appending it to the original German-like nonsense-style name, and rebuilt a new 76K database with English names. Now I am building modularized diagrams (no more than 2 dozens of tables per diagram) with table names only...oh, and to preclude further questions about relationships, - yes, table names and relationships.

    So, thank you all for the smart-@$$ answers and very useless references
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you are welcome!

    seriously though, I don't think the representation of the diagrams is understood even by the SQL Server team at MS. IIRC the diagramming component came out of the Visual Studio team, not SQL Server. and I am 99% sure there is no xml representation either. It's just some proprietary binary format that is not meant to be exposed to clients. The only sanctioned viewer is SSMS itself.

    But here is something that may help, because I do have a little experience with creating ERD from scratch, in my own app (SqlSpec). I use the system catalog to find how the tables are linked up via foreign keys, then use GraphViz to create an ERD from that. GraphViz is a fairly powerful graphing package coming from AT&T, and it's free. You define the graphs using a language called DOT, it's very simple to learn.

    There is also at least one open source package that uses graphviz to create similar diagrams, one on sourceforge called schemaspy for example.

    So my advice is to abandon sysdiagrams and use graphviz.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rdjabarov View Post
    It is the notorious SAP database structure
    Hell, you should have lead off with that tidbit!

    Quote Originally Posted by rdjabarov View Post
    So, thank you all for the smart-@$$ answers and very useless references
    My particular specialità,
    For you, no charge!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Here's an example of an ERD I made programmatically with graphviz. only a few tables but you get the idea. with 75k tables it would look like absolute garbage, but I guess you know that already.

    http://elsasoft.org/images/alltables...s_detailed.png

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •