Eden Ridgway's Blog

.Net and Web Development Information

  Home :: Contact :: Syndication  :: Login
  105 Posts :: 1 Stories :: 78 Comments :: 3 Trackbacks

Search

Article Categories

Archives

Post Categories

Development

General

When working on a new system you usually want to be able to quickly get a simple view of the database. Usually this involves using Enterprise Manager (or Management Studio) to create a diagram and auto-arrange the tables for you. However it usually does a pretty poor job of this and any diagram you print ends up spanning multiple pages. What I wanted is something that would do a decent job of arranging an ERD for me. I decided that a good candidate for this was GraphViz since it has a variety of layout engines and the DOT file format (that it uses to generate graphs from) is really simple.

So I put together a SQL script and a batch file and generated the following graphs for Northwind.




For those of you who don’t care about the implementation details, here are the files: GraphVizGenerator.zip. The batch file assumes that osql is in your path, that you have used the default install directory for GraphViz and that you use integrated authentication to connect to your server, so you will need to change this if this is not the case. To use the batch file, run the following command:

"Generate Diagram.bat" ServerName DatabaseName

For example:

"Generate Diagram.bat" (local) Northwind

Okay, so how does it all work? Well the SQL script uses union statements to return a row per line in our DOT file, like this:

SELECT
'digraph G {'

UNION ALL

SELECT
' "' + TABLE_NAME + '"[shape=box];'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN ('dtproperties', 'sysdiagrams')

UNION ALL

SELECT
' "' + FKRefTable + '" -> "' + PKTable + '";'
FROM
#
Dependencies

UNION ALL

SELECT
'}'

In a step before this I use the INFORMATION_SCHEMA constraints tables to populate a temporary table (#Dependencies) with the relationships (to keep the code simple). I dump these results into a text file using the following command:

osql -S %1 -d %2 -E -n -w 1000 -h-1 -i "GraphViz Generator.sql" -o "ERD.dot"

Note that I use the –h-1 parameter to ensure that the headers are not dumped into the file. The –w 1000 also ensures that no wrapping of the columns occur.

This generates a dot file that looks something like this:

digraph G {
"Categories"[shape=box];
"CustomerCustomerDemo"[shape=box];
"CustomerDemographics"[shape=box];
"Customers"[shape=box];
"Employees"[shape=box];
"EmployeeTerritories"[shape=box];
"Order Details"[shape=box];
"Orders"[shape=box];
"Products"[shape=box];
"Region"[shape=box];
"Shippers"[shape=box];
"Suppliers"[shape=box];
"Territories"[shape=box];
"CustomerCustomerDemo" -> "CustomerDemographics";
"CustomerCustomerDemo" -> "Customers";
"Employees" -> "Employees";
"EmployeeTerritories" -> "Employees";
"EmployeeTerritories" -> "Territories";
"Order Details" -> "Orders";
"Order Details" -> "Products";
"Orders" -> "Customers";
"Orders" -> "Employees";
"Orders" -> "Shippers";
"Products" -> "Categories";
"Products" -> "Suppliers";
"Territories" -> "Region";
}

This is then used to spit out five ERDs using different layouts so that you can choose the one that you find most useful.

posted on Friday, January 05, 2007 7:16 AM

Feedback

# re: Using GraphViz to Generate ERDs for SQL Server 1/5/2007 8:32 AM Chuck Norris
Awesome work Eden. You made Chuck proud!

# re: Using GraphViz to Generate ERDs for SQL Server 1/5/2007 6:38 PM Roché Naudé
Eden, this is FANTASTIC. No more pulling out my hair about those terrible diagrams in SQL Server.

Well done...

# re: Using GraphViz to Generate ERDs for SQL Server 2/20/2007 5:36 AM lb
this is way cool.

# re: Using GraphViz to Generate ERDs for SQL Server 2/20/2007 5:42 AM lb
>any diagram you print ends up spanning multiple pages

hang on -- here's a tip about enterprise manager
(see http://secretgeek.net/sql_ent_page.asp)

Right-Click and select the option 'View Page Breaks'. Now you can rearrange your tables to avoid page breaks. And a neat print out is as easy as pie.

It took me years to notice this option -- and i regularly get comments from people who also missed this for years.

Still -- your use of GraphViz is extremely cool.




# The View Page Breaks feature 2/22/2007 7:19 AM Eden Ridgway
Thanks lb. I've found that that even with the "View Page Breaks" feature it can still be quite a mission :). Obviously when it comes to small databases it's not really an issue, but when you are looking at 70+ tables it is a different story. What I normally do for larger databases is create several diagrams that focus on functional areas of the system and arrange them so that they each fit on a page.

You advice is appreciated though. Thanks.

# Using GraphViz to Generate ERDs for Oracle (PLSQL) 11/10/2009 9:03 AM
Using GraphViz to Generate ERDs for Oracle (PLSQL)

Comments have been closed on this topic.