Blog

How to Create a Database Diagram in SSMS

By Updated

If you work on SQL Server, there's no need to install anything to see an ERD. SQL Server Management Studio ships with database diagrams, and turning existing tables into a picture takes a few clicks.

The first use of SSMS creates diagram support objects in the database schema. After that, add existing tables, choose how much detail to display, and copy the diagram as an image or generate schema-only DDL. Comparable desktop workflows are covered for MySQL Workbench and A5:SQL Mk-2.

First: setting up diagram support

Expand a database in Object Explorer and you'll find a Database Diagrams node. The first time you expand it, SSMS asks whether you want to set up database diagramming. Answer yes and it installs what it needs.

That step creates the following objects in that database:

  • the sysdiagrams table
  • the sp_creatediagram, sp_alterdiagram, sp_dropdiagram and sp_renamediagram stored procedures
  • the sp_helpdiagrams, sp_helpdiagramdefinition and sp_upgraddiagrams stored procedures
  • the fn_diagramobjects function

They exist so diagrams can be stored inside the database itself. Your business tables aren't touched, but objects are being added to the schema, so on a production database this belongs in your normal change process.

Setup requires membership in the db_owner role. That's deliberate — it's how access to diagrams is controlled. Connected with a read-only account, you won't get the prompt, and the DDL route further down is the way forward.

Generating an ER diagram in SSMS

Once support is installed:

  1. Right-click Database Diagrams and choose New Database Diagram.
  2. The Add Table dialog lists the tables in the database. Select the ones you want and click Add, or double-click names to send them to the canvas.
  3. Close the dialog and the diagram is there, with relationship lines already drawn between tables connected by foreign keys.

To bring in more tables later, right-click empty canvas and choose Add Table again. Removing a table from the diagram takes it off the picture only; the table itself stays in the database.

Putting a several-hundred-table schema on one sheet produces something nobody can read. Separate diagrams per domain (members, orders, billing) are far more useful in practice. Diagrams are stored in the database, so naming them clearly means you can reopen the right one later.

Reading the relationship notation

One thing to know up front: SSMS diagrams mark relationship ends with a key icon and an infinity symbol. The key sits on the parent side holding the primary key; the infinity symbol sits on the child side, where many rows can match.

That's a different visual language from the crow's foot (IE) notation most online ERD tools use, though the meaning is identical. If you'll be comparing an SSMS diagram against one drawn elsewhere, the ERD notation guide maps the symbols across notations.

Clicking a relationship line shows, in the properties pane, which columns are joined and what the delete and update rules are. On an inherited database, that's a fast way to learn how the foreign keys actually behave.

Controlling how much each table shows

By default every table displays column names with data types, so the canvas fills up after about ten tables.

Right-click a table header and choose Table View to change that:

  • Standard. Column names, data types and nullability. Use it when you're studying one area closely.
  • Column Names. Names only, no types. The best balance for reading structure.
  • Keys. Primary and foreign key columns only. Ideal when relationships are the point.
  • Name Only. Just the table name, for fitting a whole schema on screen.
  • Custom. Pick the fields yourself.

A practical rhythm is to start in Name Only or Keys to see the shape of the schema, then switch the tables you care about to Standard. Tables move by dragging, and the arrange command on the canvas context menu spreads out overlapping tables for you.

Editing schema from the diagram, carefully

The SSMS database diagram is a designer rather than a viewer. From the canvas you can create, edit and delete tables, columns, keys, indexes, relationships and constraints.

That convenience deserves a warning. Saving applies the changes to the real schema. If you opened the diagram to look at structure, close it without saving. Connected to production, treat every drag as a potential change: moving a column or deleting a relationship line and then saving does exactly what it looks like.

If you do plan to make design changes here, script the affected tables first so you have something to compare against.

Splitting a schema across several diagrams

Because diagrams are stored inside the database, you can keep several and open whichever fits the question you're answering. Past a few dozen tables, one sheet stops working, so it's worth planning for several from the start.

Agree on a naming rule. Prefixes like 01_members, 02_orders keep the list in a sensible order and tell a newcomer where to start. If a diagram exists to preserve a moment in time, put the date in the name — orders_2026-08 — so it can be compared later.

Put boundary tables on both sheets. If the orders diagram shows the members table, even in Name Only view, nobody loses track of where the relationship goes. The same table appearing on several diagrams is fine: there's one table, drawn more than once.

A diagram isn't a snapshot. It reads the current schema every time it opens, so a column someone added yesterday is already there today. To preserve a specific point in time, export an image or keep the DDL from that moment alongside it.

Relationship labels and page breaks

A few more items on the canvas context menu are worth knowing, because they change how the finished picture reads.

Show relationship labels. Names of the foreign key constraints appear along the lines. On a schema where constraints are named by convention — FK_posts_members — you can read the relationships without clicking anything. Where constraint names are auto-generated noise, leave labels off.

View page breaks. Dotted lines show where a printout would be cut. If the diagram is going to a meeting on paper, turn this on before arranging tables and you won't print a relationship line severed at a page edge.

Zoom. With many tables, the rhythm is to zoom out for the overall flow and back in to read columns in one area. Combined with switching Table View to Name Only, it makes even large schemas workable.

Reaching indexes and constraints from the diagram

The diagram shows tables and relationships, but there's more available without leaving it. Right-click a table and you can open its indexes and keys, its relationships, and its check constraints.

That's a fast path when you're investigating why a query is slow on an inherited database: find the table on the diagram, open the index list, check which columns are indexed and in what order, then compare that against the query's filters. Column order decides whether a composite index applies, so read the order, not just the names.

The relationships dialog shows delete and update rules per foreign key. What happens to a member's posts when the member row goes away is written right there, which usually beats reading application code to find the same answer.

Designing a new table on the canvas

The diagram isn't only for reading. You can build new tables here too:

  1. Right-click empty canvas, choose to add a new table, and name it.
  2. Fill in column names, data types and nullability in the grid.
  3. Select the column that will be the primary key and use the set-primary-key button on the toolbar.
  4. Drag from the parent table's primary key column onto the child table's foreign key column to create the relationship.
  5. Save, and the changes reach the database.

One mistake shows up often here: dragging the relationship in the wrong direction swaps parent and child. Check that the primary key table and foreign key table in the dialog match what you intended before continuing. How to decide which side is the parent is covered in the relationships section of How to Draw an ERD.

If the design is new rather than existing, settling naming and type conventions first makes the rest faster. Those criteria are laid out in Database Table Design Checklist.

Getting the diagram out as an image

Finished diagrams usually end up in a review deck or a handover document. The diagram window has no export-to-file command; it goes through the clipboard.

Right-click empty space on the canvas and choose Copy Diagram to Clipboard. The whole diagram lands there as an image, ready to paste into Word, PowerPoint or an image editor and save in whatever format you need.

When the schema is too wide for one screen, switch Table View to Name Only and tidy the layout before copying. If the target is print, turn on page breaks first so you can see where the diagram would be cut.

Scripting the schema as DDL

A diagram lives inside SSMS. To move the schema into another tool or turn it into documentation, you need DDL. On SQL Server, structure-only scripting works like this:

  1. Right-click the database in Object Explorer and choose Tasks → Generate Scripts.
  2. Choose whether to script the entire database or select specific tables.
  3. On the scripting options page, click Advanced and set Types of data to script to Schema only.
  4. Output to a file or a new query window, and you have CREATE TABLE statements.

Leaving row data out usually makes the file smaller, but table and column names and constraints still reveal system structure. Treat the generated script as internal information even though it contains no business rows. It can then be used with another ERD tool's SQL import. The equivalent extraction commands for MySQL and PostgreSQL are collected in step 1 of SQL to ERD.

If you live in VS Code

There's one more option that avoids opening SSMS at all. Microsoft's MSSQL extension now includes a Schema Designer, generally available as of 2026. It gives you a visual surface for creating and editing tables, foreign keys, primary keys and constraints, with search, drag and drop, zoom, a mini-map and auto-arrange.

Extension-by-extension coverage of ERD work inside the editor is in Drawing ER Diagrams in VS Code.

FAQ

SSMS asks whether to set up diagramming. Is it safe to say yes?

It's the normal first-run prompt. Saying yes creates a sysdiagrams table plus the diagram stored procedures and a function in that database. They exist to store diagrams and don't touch your business tables, though on a production database it's worth running it through your usual change process. Only members of the db_owner role can perform the setup.

If I edit a table in the diagram, does the real database change?

Yes. The SSMS database diagram is a designer, not a viewer. Adding a column or drawing a relationship and then saving alters the actual schema. If you only opened it to look, close without saving, and be especially careful while connected to production.

How do I save the diagram as an image file?

Right-click empty space on the diagram surface and choose Copy Diagram to Clipboard, then paste into Word, PowerPoint or any image editor and save from there. There's no direct export-to-file command in the diagram window.

How do I script just the schema as DDL?

Right-click the database in Object Explorer and run Tasks, then Generate Scripts. On the scripting options page click Advanced and set Types of data to script to Schema only. You get CREATE TABLE statements with no data, which keeps the file small and makes it easy to paste into another tool.

For an image, install diagram support, create a diagram, add the tables, adjust the table view and copy it to the clipboard. To use the structure outside SSMS, generate schema-only DDL instead.

With that DDL in hand, pasting it into WorksCove ERD gives you an editable diagram and a table specification from the same schema, and a SQL Server schema can be exported back out as MySQL, PostgreSQL or Oracle DDL. The paste-to-result path is covered with screenshots in SQL to ERD.