Methods to generate ER diagrams programmatically

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.

Any help would be highly appreciated.

Thanks

-Akash

Hi Akash,

I worked on this a couple of years ago with SQL 2000. I wrote an application using Java, then started to rewrite it in VB.NET. I programmatically collected ER data from SQL Server and then used Graphviz to build the diagrams. It worked well, but I never finished it.

Chris

|||

Hi Chris,

Thanks for the immediate response, would be higly obliged if you would be able to demonstate the code for collecting the ER diagram from SQL Server.

-Akash

|||

Like I said, I didnt work all the bugs out, but what I was able to create was an input file for Graphviz. I created a tables collection and added one object for each table. then, each table object had a columns collection which contained one object for each column. I had some additional properties to denote PK and FK columns. I then used SQLDMO to iterate through the SQL Server database and populate my collections of objects. I then wrote a second procedure which built the input file for Graphviz.

Generally the code looks like this:

For each table

add an item to the tables collection

add a columns collection to the table

add each table column to the columns collection

next column

next table

I'll try to post an output from Graphviz if you are interested.

Chris

|||

Hi Akash,

I found my code and checked it out. Currently, it works against both SQL 2000 and SQL 2005. I pointed it to the AdventureWorks sample database on a SQL 2005 box and it worked. If you will give me your email address I will send you the generated JPG output file.

|||

Hi Chris,

It would be really helpful. Do send it on lionhearted.prince@.gmail.com

Thanks

-Akash

|||

Hi Akash,

Was that output file what you were looking for?

|||

Hi Chris,

It was really great to see the output file, but that wasn't what exactly i needed. I need to generate ER diagrams programmatically using SQL Server without using a third party tool.

If you have any pointers to get it done, do share it.

Thanks

-Akash