You may (or may not) know that the Database Diagrammer of SSMS stores the created diagrams in a table called dbo.sysdiagrams.
This table has always been a mystery to me, especially the column “definition”, as it contains binary data, and documentation cannot be found about its format.
So when I tried to figure out what all the bytes meant, and my standard editor plus hex viewer plug-in (read: Notepad++) could not help me any further, I searched and found FlexHEX which promised to provide the functionality to annotate and structure binary data. Somehow this did not work out as expected – I could not figure out how to create and edit “structures” – but as I clicked around, I hit the menu “Open OLE Compound File…” as a last resort. What could go wrong.
To my great surprise, this command actually opened the file and displayed a file structure, with each of the sections containing some data.
As it turned out, the section DSREF-SCHEMA-CONTENTS was the one I was looking for, as it contains, as I later found out, all the textual information in the diagram:
- (original) diagram name
- connection string
- list of all tables in the diagram (as of last save)
This section is stored as a Microsoft Compound File or Compound File Binary Format or Compound Document File, and the easiest way to process it in C# is the nuget package OpenMcdf (source on GitHub).
Writing a small tool, I verified that the sections from the binary data really have those names, and are not artifacts of the hex editor.
Once I got hold of the data contained in section DSREF-SCHEMA-CONTENTS, making sense of the structure of this data was quite straight-forward.
Pingback: SMOscript 0.50 | devioblog