A good friend of mine (and ex-colleague) Eden Ridgway did all the hard work in terms of creating something that will do a "decent job of arranging an ERD" for a database. If you're using SQL Server, go have a look at his post Using GraphViz to Generate ERDs for SQL Server. As Eden mentions, Management Studio doesn't do the most stellar job of creating a quick and practical ERD of your database.
I've been working with Oracle for the past few months on an integration project (my first time using PLSQL - out of my comfort zone, let me not start on that here!!! I'm a T-SQL girl at heart). So - long story short - I've written a basic PLSQL script that creates the equivalent GraphViz DOT version file content from an Oracle database.
Now, bear in mind that schemas/rights work slightly differently in Oracle so this script basically generates the file content based on everything that the logged in user is allowed to see. You can further restrict what is included in sections 1.1 and 1.2 in the script (read the comments for instructions).
Here's the script:
/*
PLSQL Oralce
*/
/********************************************************************
Generate the GraphViz ERD
********************************************************************/
SELECT
'digraph G {'
FROM DUAL
UNION ALL
/**************************************
Set the shapes
***************************************/
SELECT
' "' || OBJECT_NAME || '"[shape=box];'
FROM
ALL_OBJECTS
WHERE
OBJECT_TYPE = 'TABLE'
/*1.1)Enable this block using -- and update list if tables should be restricted to specific owners
AND OWNER IN (--'SYS','SYSTEM',
--include current schema for current session
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
--*/
UNION ALL
/**************************************
Add the relationships
***************************************/
SELECT
' "' || FKRefTable || '" -> "' || PKTable || '";'
FROM
/********************************************************************
Get all the foreign key relationships in the database
********************************************************************/
(SELECT
PKTABLE.TABLE_NAME as PKTable,
FKTABLE.TABLE_NAME as FKRefTable
FROM
ALL_CONSTRAINTS PKTABLE
INNER JOIN ALL_CONSTRAINTS FKTABLE ON
PKTABLE.CONSTRAINT_NAME = FKTABLE.R_CONSTRAINT_NAME
WHERE
PKTABLE.CONSTRAINT_TYPE = 'P'
AND FKTABLE.CONSTRAINT_TYPE = 'R'
/*1.2)Enable this block using -- and update lists if tables should be restricted to specific owners
AND PKTABLE.OWNER IN (--'SYS','SYSTEM',
--include current schema for current session
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
AND FKTABLE.OWNER IN (--'SYS','SYSTEM',
--include current schema for current session
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
--*/
ORDER BY
FKRefTable)
UNION ALL
SELECT
'}'
FROM DUAL;
Here's a VERY BASIC sample DOT file content for a test database I used (based on only including the current session's current schema):
digraph G {
"ORDERS"[shape=box];
"PRODUCTS"[shape=box];
"CUSTOMERS"[shape=box];
"ORDER_ITEMS"[shape=box];
"ORDERS" -> "CUSTOMERS";
"ORDER_ITEMS" -> "PRODUCTS";
"ORDER_ITEMS" -> "ORDERS";
}