2

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

6

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.