Overview

Database design can often feel like navigating a maze of tables, relationships, and data types — especially when working directly with T-SQL scripts. But what if you could map out your database, make changes to your tables, and manage complex schemas within a graphical interface?

We are excited to announce the Public Preview of Schema Designer in the MSSQL extension for Visual Studio Code!

Features

The Schema Designer feature offers these features:

• Visualize database structure with interactive diagrams.
• Create or edit tables, foreign keys, primary keys, and constraints.
• Search, drag & drop, filter, zoom, use a mini-map, and auto-arrange diagrams for efficient navigation and customization.
• Export your schema diagrams to share with your team or include in documentation.
• Automatically generate and view read-only T-SQL scripts representing your schema changes.
• Review and apply changes to the database with the ‘Publish Changes’ feature.

Open Schema Designer

Right-click on the database in the object explorer and select “Design Schema (Preview)” from the menu. This opens the schema designer view, which then allows you to see the visual database diagram.

Once inside the Schema Designer, you find a flexible and intuitive canvas with various navigation capabilities. Here’s how to get around:

• Pan and Zoom: Select and drag anywhere on the canvas to pan across the diagram. Use your mouse scroll wheel or trackpad gestures to zoom in and out for a closer or broader view.
• Mini-Map: Use the built-in mini-map (located in the bottom right corner of the designer) for quick navigation across large or complex schemas.

• Drag & Drop: Rearrange tables and relationships easily by dragging elements on the canvas. This helps you create a layout that makes sense to you or your team.
• Search & Filter: Use the search box (Ctrl+F or Cmd+F) to quickly locate specific tables or columns. Apply filters to focus on certain parts of the schema or hide irrelevant elements.
• Auto Arrange: The diagram is automatically arranged in a clear and readable layout by default. If you’ve manually repositioned tables and want to reset the view, select the ‘Auto Arrange’ button to reorganize the tables into the default optimized layout.

Understanding Table Structure and Relationships

Once you enter the schema designer view, you see the visualization of your database tables. Each table displays its schema and table name, columns, data types, and primary keys shown as a key icon.

Foreign key relationships are shown with connecting arrows between columns. For example, in the diagram above, the AddressID column in the CustomerAddress table references the AddressID column in the Address table—visually representing the relationship between them.

Add or Edit Tables

To add a new table, select the ‘Add Table’ button in the top toolbar. To edit an existing table, select the pencil icon on the table you want to modify directly in the diagram.

This action opens the ‘Table’ tab in the Table Editor in a side panel, where you can:

• Select or change the schema
• Define the table name
• Add new columns with name, data type, default value, and constraints
• Mark one or more columns as primary keys
• Delete or update existing columns as needed

Once you’ve made your changes, select ‘Save’ to apply them. The diagram will instantly update to reflect your changes. For more advanced table editing capabilities, such as creating indexes or setting up constraint rules, you can also use the Table Designer available in the MSSQL extension for VS Code.

Add or Edit Foreign Key Relationships

To manage foreign key relationships, select the ellipsis (…) on a table in the diagram and select ‘Manage Relationships’.

This opens the ‘Foreign Keys’ tab in the Table Editor side panel, where you can:

• Add new foreign key relationships by referencing primary keys in other tables
• Define the foreign key name
• Edit existing foreign keys to update or correct relationships

Changes are automatically reflected in the visual diagram, with arrows showing the direction of each relationship.

Alternatively, you can also create a relationship by dragging an arrow from one column to another directly in the diagram. This intuitive method quickly defines a one-to-one relationship between the selected columns.

View Schema Definition in Script Pane

In the ribbon, you can select the ‘View Code’ button in the ribbon to open the bottom pane for the read-only T-SQL script that shows actions performed on the schema designer GUI in real time.

Review and Publish Your Changes

Once you finish editing tables or relationships, select the ‘Publish Changes’ button in the top toolbar. This generates a change summary report listing all pending modifications to your schema.

Review the report carefully and check the confirmation box to acknowledge and accept any potential risks associated with applying the changes. This process is powered by DacFX (Data-tier Application Framework), which ensures your schema updates are deployed smoothly, reliably, and with minimal disruption to your database.

Next Steps

• Install the MSSQL extension for Visual Studio Code if you haven’t already: Learn how
• Watch the demo video on YouTube: Watch now
• Report Bugs: Open an issue
• Submit Feature Requests: Suggest an feature
• Connect and discuss with other developers: Join the discussion