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.