Drawing a Diagram of an Existing Database

An unfamiliar database looks less mysterious when you can see its tables together. A diagram of an existing database gives you a useful map, provided the lines reflect real foreign keys. Start with the relationships SQL Server actually enforces.

Scope the Diagram of an Existing Database to One Question

Do not open every table and expect the result to become readable. Start with one business question, such as which tables carry an order from entry to payment. List the likely tables, then add their immediate neighbors. A diagram of an existing database built for one workflow is easier to review than a wall-sized picture of the entire schema. Save a separate view for each process that needs explanation.

I usually begin with the table whose data a support ticket names. Then I follow declared relationships outward. Ask yourself: which join would you trust if the application source disappeared? The answer should be backed by keys or by a documented rule. Similar column names are hints, not proof. A database can have several CustomerId columns with different meanings.

Use the Diagram Tool Carefully

In SSMS, expand the database and its Database Diagrams folder, then create a new diagram and add the tables in scope. If SSMS asks to install diagram support objects, get approval through the normal change process before allowing it. The diagram is stored in the database, and the designer can also change schema. Use a development copy when you are learning an unfamiliar system, or open an existing diagram without making changes.

Place central tables near the middle and move lookup tables to the edge. Hide columns you do not need for the question at hand. I have watched a tidy diagram become useless after one click that added every related table. The undo button deserves a chair of its own. Keep the view small enough that a reader can follow each line.

Confirm Which Lines Are Enforced

The diagram draws relationships from foreign key metadata. Query sys.foreign_keys to see the same declared constraints without relying on the picture. The first query lists parent and referenced tables in the current database. Run it under the database you are documenting. An empty result means no visible foreign keys were returned, not that the application has no relationships.

A line based on a foreign key is useful evidence, but inspect whether the constraint is disabled or not trusted. A disabled relationship does not protect new data. An untrusted relationship can affect assumptions about existing data and the optimizer. Put that state in the written notes beside the diagram.

SELECT
    fk.name AS ForeignKeyName,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ParentSchema,
    OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
    fk.is_disabled,
    fk.is_not_trusted
FROM sys.foreign_keys AS fk
ORDER BY ChildSchema, ChildTable, fk.name;

Read the Actual Column Pairing

A relationship between two tables can use more than one column. The line alone does not tell you the column order or the exact join predicate. The next query expands each foreign key into its paired columns and ordinal position. That is particularly useful when a developer copied a table name into a document but left out the tenant key. The missing key can turn a valid looking join into duplicate rows.

I check this query whenever a relationship line looks too simple for the business rule. It also helps distinguish two foreign keys pointing to the same parent table, such as CreatedBy and ApprovedBy. Keep the column mapping in your notes, not just a screenshot.

SELECT
    fk.name AS ForeignKeyName,
    fkc.constraint_column_id,
    pc.name AS ChildColumn,
    rc.name AS ParentColumn
FROM sys.foreign_key_columns AS fkc
JOIN sys.foreign_keys AS fk
  ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS pc
  ON pc.object_id = fkc.parent_object_id
 AND pc.column_id = fkc.parent_column_id
JOIN sys.columns AS rc
  ON rc.object_id = fkc.referenced_object_id
 AND rc.column_id = fkc.referenced_column_id
ORDER BY fk.name, fkc.constraint_column_id;

Treat Missing Lines as Questions

Many applications enforce relationships in code rather than with foreign keys. Imports, event tables, legacy schemas, and cross-database references create other gaps. A missing line in SSMS does not prove there is no business relationship. Look at the application queries, stored procedures, naming patterns, and data definitions. Mark a relationship as inferred until someone verifies its rule and scope.

Do not draw an inferred line with the same visual meaning as an enforced constraint. A solid foreign key and a guessed join answer different questions. If the diagram is for a migration, separate the two categories in the handoff. That distinction saves someone from creating a foreign key on dirty legacy data because the drawing looked authoritative.

Add Context a Picture Cannot Carry

A diagram of an existing database shows structure, not workload. It does not tell you which table is hot, which index supports a join, or whether a view filters rows for security. Add short notes about key queries, ownership, data classification, and unusual rules. For a large table, note its growth pattern from measured evidence rather than writing an unsupported number on the drawing.

The same diagram also cannot tell you which application feature reads a table. I pair it with a small query inventory or code search when impact analysis is the goal. A diagram answers “what joins what.” It only partly answers “what breaks if I change this.”

Keep the Diagram of an Existing Database Aligned With the Schema

After a schema change, reopen the diagram and reconcile its layout with current metadata. New foreign keys can add relationships, while dropped keys remove lines. A stored diagram can grow stale if nobody owns it. Put a review date and database version in the surrounding document. If you share a screenshot, name the source database and the date. Otherwise an old picture passes for the current system.

I prefer several small diagrams over one permanent master chart. Each small view has an audience and a question. When that question changes, update or retire the view. A picture that once helped can become an excellent way to mislead at scale.

Use the Diagram of an Existing Database as a Starting Point

Take the diagram to the developer or DBA who knows the workflow and ask them to challenge the inferred edges. Confirm data paths with queries in a safe environment. Check joins on realistic data and inspect rows that fail the expected match. The diagram should guide investigation, not close it.

When the reviewed version is ready, save the SSMS diagram and a shareable image or document under the project rules. Include the key query results or definitions that support important lines. The next reader then has a route from the picture back to the database. That route is the difference between a useful map and decoration.

Related reading on this blog: Database Diagram: Available Again in SQL Server Management Studio 18.1 Onwards and Find Untrusted Foreign Key.

A database diagram is not the database contract, it is a map whose important lines you can verify.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.