Is it possible to script the creation of a database diagram using T-SQL?
If not, what is the Database Diagram Designer doing when you use it to save / edit a diagram (is it using PowerShell or some other language to save and load these type of objects)?
I ask because I'm looking to automate the creation of database diagrams. I have a tool one of my developers wrote (in C#) which automatically creates tables and their foreign key relationships based on the responses of some web service calls it makes to our vendors' data.
1 Answer 1
The Database Diagram objects are indeed created using T-SQL. You can see what SSMS does when creating, editing, and saving diagrams by running a T-SQL trace against your development SQL Server while using the Database Diagram tools.
A database that has the Database Diagram objects enabled will contain a dbo.sysdiagrams table.
Many properties use by the Database Diagram system are stored in Extended Properties. These can be seen looking at sys.extended_properties in the diagram-enabled database.
The following objects are also engaged in the Database Diagram system:
dbo.sp_upgraddiagrams dbo.sysdiagrams dbo.sp_helpdiagrams dbo.sp_helpdiagramdefinition dbo.sp_creatediagram dbo.sp_renamediagram dbo.sp_alterdiagram dbo.sp_dropdiagram
Hopefully the above details will get you on the road to reverse-engineering the Database Diagram system, however be aware the actual diagrams are stored in a binary format that is, as far as I can tell, not documented.