How to Export an SQL Server Database Schema Into a Diagram

If you have a database in SQL Server, you can generate its database diagram to visualize your data. Better yet, you can export your database as an SQL DDL script and use it to create a data model in Vertabelo where you can process it further. Read on to find out more.

In this article, we review how to export SQL Server database schema into a diagram. First, we walk through how to create a database diagram in SQL Server, either for the entire database or for specific schemas of the database. Next, we explain how to export a database or its schemas from SQL Server as an SQL DDL script. Finally, we import this script into Vertabelo and create a database model for further modifications.

Let’s get started.

Database Diagrams in SQL Server

Let’s use the AdventureWorks sample database from Microsoft. This database contains several schemas with tables. Download it from the Microsoft official website and restore it in the SQL Server Management Studio (SSMS) to use as you follow along.

How to Create a Database Diagram in SQL Server

Follow these steps to create a new database diagram in SQL Server.

Expand the AdventureWorks database in the Object Explorer and right-click on the Database Diagrams to create a new database diagram, as shown below.

A window with all database tables pops up.

First, choose all the tables you want to include in the database diagram. Then, click on the Add button and wait until all tables are loaded. When ready, click on the Close button. You see all the tables along with the links between them.

Let’s try it out using different schemas available in the AdventureWorks sample database.

Database Diagram of the HumanResources Schema

Let’s follow the steps below to create a database diagram of the HumanResources schema.

The schema contains six tables. Let’s select all of them as shown below.

Click the Add button and wait for all tables to load. Then click on the Close button to see the database diagram, which looks like this:

Let’s go through this database diagram of the HumanResources schema.

The Employee table is a central table that stores data about employees. Its extensions are the tables JobCandidate, EmployeePayHistory, and EmployeeDepartmentHistory, storing respective data for each employee. The EmployeeDepartmentHistory table contains the ShiftID column that links to a specific shift in the Shift table and the DepartmentID column that links to a specific department in the Department table, among other columns.

Database Diagram of the Person Schema

Let’s follow the steps below to create a database diagram of the Person schema.

The Person schema contains 13 tables. Let’s select all of them as shown below.

Click the Add button and wait for all tables to load. Then click on the Close button to see the database diagram, which looks like this:

Let’s go through this database diagram of the Person schema.

The Person table is a central table that stores data about persons. It links to other tables that store more data related to the persons. I encourage you to go over this database diagram and try to understand each link between the tables.

There are various database diagram tools for SQL Server for creating or modifying database diagrams. Check out this article to learn more about such tools.

Let’s now get the SQL DDL scripts for both HumanResources and Person schemas.

How to Get an SQL DDL Script of a Database in an SQL Server

Follow the steps below to export an SQL Server database into an SQL DDL script.

In the Object Explorer, right-click on the AdventureWorks database, Tasks, and then Generate Scripts… as shown below.

A wizard window pops up. Click on the Next button.

SQL DDL Script for the Entire Database

Let’s first choose to generate a script for the entire database as shown below. Later, we will see how to generate scripts for specific schemas.

Click on the Next button.

There are four options for saving the script as shown below. Here, we choose to save it as a .sql file. Click on the Next button.

Check out this article to learn more about the various options for saving scripts.

It presents a summary of the selected options as shown below. Review it and click on the Next button.

It takes a couple of minutes to execute. After that, you should see that all actions are successfully completed as shown below. Click on the Finish button.

The exported file is saved in the indicated location.

SQL DDL Script for the HumanResources Schema

Now, let’s follow the steps to export a specific SQL Server schema into an SQL DDL script.

This process is similar to exporting the entire database, only that specific objects are selected to be exported. Here, we choose the HumanResources schema as shown below.

After following the prompts, you find the exported script file in the location indicated.

SQL DDL Script for the Person Schema

Now, let’s follow the steps to export another SQL Server schema, Person, into an SQL DDL script.

Choose the objects to be exported as shown below.

After following the prompts, you find an exported script file in the location indicated.

Create a Model in Vertabelo From an SQL DDL Script

Let’s now work with these SQL DDL scripts using Vertabelo as an ERD tool for SQL Server.

Vertabelo is a data modeling tool that offers a wide range of features. You can modify your database diagram here and then export it as SQL DDL for further use in your database.

Here is an article on how to create a database diagram from a DDL script. Also, check out this article on DDL scripts and how to use them if you want to learn more about the topic.

Let’s see how to use the script for the HumanResources schema to create a diagram using Vertabelo as an ERD tool.

Step 1. Click on the Create new document button in Vertabelo.

Step 2. Choose Physical data model as the document type as shown below and click on the Next button.

Step 3. Name the physical data model and choose the database engine. Also, select From SQL as the content type and import the script file as shown below.

Step 4. Click on the Import SQL button. Don’t worry about the SQL parsing issues at this point. Then, click on the Start modeling button.

Step 5. Below is the physical data model of the HumanResources schema.

The DDL script exported from SQL Server contains several commands that are not standard DDL commands, such as USE, SET, EXEC, and GO. It causes Vertabelo to not import all objects correctly. Some columns may be missing due to the SQL parsing issues encountered during the import process. These columns must be added manually.

This model creation process is the same for the Person schema. Here is what we get for the Person schema:

In the left-hand pane, there are some warnings for Tables and References just like for the HumanResources schema. Due to the SQL parsing issues, some columns must be added manually.

You may now modify the imported model in Vertabelo as needed. If you want to export an SQL DDL script to recreate your database, here is how to generate an SQL script in Vertabelo. You can find more insight on the topic of SQL script creation in Vertabelo.

Why Use Vertabelo as a Database Diagram Tool for SQL Server?

SQL Server generates a database diagram based on what data is contained within a database. It does not offer extensive features for modifying the database diagram.

Here comes Vertabelo! You can export your database or schema as an SQL DDL script, import it into Vertabelo, and work on your database model.

Now that we have reviewed how to go from an SQL Server database to a data model in Vertabelo, let’s review the features Vertabelo offers.

• Model validation: Vertabelo validates your model from a technical point of view. For example, it notifies you if a table is missing a primary key.
• Features for a better database design:
  • Subject areas: It helps you keep your database diagram in order by creating subject areas for groups of tables. Learn more about subject areas here.
  • Text notes: Another feature that makes your database diagram understandable at a glance. You can add text notes throughout the database model. Learn more about text notes here.
  • Shortcuts: As the database diagrams get bigger, the references among the tables become more complex. Vertabelo lets you create a copy of any table and place it elsewhere in your database diagram to see all the references. Learn more about shortcut tables here.
• Model versioning: You can store multiple model versions and restore any version at any time. Find out more about the model versioning feature here.
• Collaboration: Vertabelo lets you share your model with colleagues, publish models online, and more. Find out about all collaboration options available in Vertabelo here.

Head to Vertabelo and see how easy it is to work with database models.

Try Vertabelo as a Database Diagram Tool for SQL Server!

You are ready to try your hand at creating database diagrams in SQL Server, generating SQL DDL scripts, and creating data models in Vertabelo by importing SQL DDL scripts. Make the best use of Vertabelo as a database diagram tool for SQL Server!

Good luck!