Create Meaningful Visualization for Large Set of Tables I'm trying to create a meaningful, usable visualization for my company's ERP database. I've extracted a skeleton of the database so I can make changes without modifying the production environment. Here are some facts:


The DB includes almost 1300 tables, approximately 500 of which have data.
Our ERP provider hasn't added any relationships to the tables.
This is an MSSQL database.


Right now we only want to generate a visualization for the tables we actually use. My question is multifold:


Is there any automated way of suggesting relationships between the tables, or will this have to be done manually?
~500 tables is too large to have in one document while being easily navigable. We're considering breaking this up into multiple diagrams for each logical unit of data (e.g. Customers, Items, Billing, Raw Materials, Inventory, etc). Is there another way that would provide logical separation with manageably sized documents?
What is the best way to generate a diagram of the tables? I know MS SSMS has a built-in function for generating diagrams, but what other options exist? The final document could be printed large sheet paper, PDF, etc.
It's possible to write a query using the database DMVs, that dynamically tries to guess relations between tables by matching column names.

Here's a very simple draft, although it will only work if your primary key column names are unique for each primary key table (i.e. if the primary keys are called "id" in all the tables, this won't fly). Also, you'll have to make a few modifications if you want the script to work with composite keys (multiple columns in the primary key).

WITH pk
AS (
    SELECT ix.[object_id], ixcol.column_id, ix.is_primary_key
    FROM sys.indexes AS ix
    INNER JOIN sys.index_columns AS ixcol ON ix.[object_id]=ixcol.[object_id]
    WHERE ix.is_primary_key=1),

     keycols
AS (
    SELECT tbl.[object_id], tbl.[name] AS tblName, col.[name] AS colName, pk.is_primary_key
    FROM sys.tables AS tbl
    INNER JOIN sys.columns AS col ON tbl.[object_id]=col.[object_id]
    LEFT JOIN pk ON tbl.[object_id]=pk.[object_id] AND col.column_id=pk.column_id)

SELECT pk.tblName AS pkTable, pk.colName AS pkCol, fk.tblName AS fkTable, fk.colName AS fkColumn
FROM keycols AS pk
INNER JOIN keycols AS fk ON
    pk.is_primary_key=1 AND
    pk.colName=fk.colName AND
    pk.[object_id]!=fk.[object_id];


Once you've established relations between the tables, you might be able to identify "clusters" of tables that you can group on separate diagrams. If there are different schemas in the database, this might be what you're looking for.

If you want to draw diagrams in SSMS, you'll have to actually create foreign key constraints between the tables, and I wouldn't really recommend doing that in a vendor database without their approval. Then again, you don't have to save the diagram.

I do all my diagramming in Powerpoint, but that's hardly the answer you're looking for. :)