Last updated: 2026-05-06

Schema Compare

Compare database schemas across SQL Server, PostgreSQL, MySQL, MariaDB, Oracle, and SQLite to identify structural differences. View changes to tables, views, stored procedures, indexes, constraints, and other objects. Generate engine-specific DDL scripts to synchronize schemas between environments.

What is Schema Compare?

Schema compare is a database tooling feature that analyzes two databases and lists every structural difference — tables, columns, indexes, views, stored procedures, functions, triggers, and constraints that differ. The output is typically a side-by-side diff plus a DDL script (ALTER, CREATE, DROP) that can synchronize one database to match the other.

Schema compare tools are used to:

• Verify that a deployment applied all expected migrations
• Promote a schema from development → staging → production
• Audit drift between environments that should match
• Generate rollback scripts before risky schema changes

Schema Compare vs Data Compare

These two Jam SQL Studio tools are often confused. They are complementary, not overlapping:

If you need both, run schema compare first (to align structure), then data compare (to align rows).

Getting Started

Schema Compare helps you understand the differences between two database schemas. Whether you're comparing development to production, or verifying a deployment, this tool shows exactly what has changed.

How to Start a Schema Comparison

1. Open Jam SQL Studio and connect to both source and target databases
2. Click Tools > Schema Compare from the main menu
3. Select the source connection and database from the dropdown
4. Select the target connection and database
5. Click Compare to analyze the differences

Recently Compared

The setup dialog remembers the source and target databases you've compared previously. Pick the most recent comparison from the Recently compared section at the bottom of the dialog to pre-fill source, target, and mode in one click.

• The most recent comparison is shown directly. Earlier comparisons collapse behind a Show N earlier comparisons toggle.
• When the expanded list contains two or more entries, a search box filters them by connection or database name.
• Each entry shows whether the comparison was a whole database or a single object, and how long ago it ran. Hover over an entry to remove it from history.
• Entries whose connection no longer exists are marked connection unavailable — clicking still pre-fills the saved values so you can re-pick the connection.

Personal Mode Limits

Schema Compare is available in both Personal and Pro modes, with different limits:

• Personal mode - Full comparisons are available when both schemas have 200 objects or fewer (tables + views + procedures + functions). Larger schemas require Pro.
• Pro mode - No schema-size limits.

Understanding the Results

After the comparison completes, the object list opens on a top-level Overview entry that's selected by default. Below it, all database objects are organized by type in a tree view. Each object is color-coded to indicate its status:

Overview

The Overview pane is a bird's-eye view of the diff. It shows totals for added, modified, removed, and identical objects across every category, plus an adaptive grid of object cards. For modified tables you see the actual changed columns inline (with type changes called out); for modified views and routines you see line-count deltas. Use the Compact / Cards / Detailed density toggle to dial in the level of detail — when there are many changes the view automatically switches to a dense layout. Click any card to jump straight to that object's DDL diff.

Viewing Object Details

Click on any object in the results to see a side-by-side diff view showing exactly what has changed. The diff highlights additions in green and deletions in red.

Generating Synchronization Scripts

Once you've reviewed the differences, you can generate a DDL script to synchronize the target database with the source schema.

Steps to Generate a Script

1. Review the comparison results and uncheck any objects you don't want to include
2. Click Generate Script to preview the synchronization script
3. Review the generated DDL statements in the preview pane
4. Click Apply to execute the script directly, or Save to export it

Schema Compare for SQL Server, PostgreSQL, MySQL, and Oracle

The Schema Compare workspace is engine-aware: it picks up the source and target connection's grammar, generates DDL in the right dialect, and matches objects by their natural identity (schema-qualified names, signatures for overloads). You can only compare two databases on the same engine — comparing SQL Server to PostgreSQL is not supported because the DDL grammars don't translate cleanly. Below is what each engine adds beyond the common feature set.

Common Schema Compare Workflows

Three concrete workflows account for most of how teams use schema compare day-to-day. Each one is a few clicks in Jam SQL Studio.

1. Promote dev → staging → production

You've added a new orders.shipped_at column and an index in development. To roll it forward:

1. Set source = dev connection / database, target = staging connection / database, click Compare.
2. Open the Overview pane — you'll see "1 column added on orders", "1 index added".
3. Uncheck anything in the diff that's not part of this change (drift you don't want to ship now).
4. Click Generate Script. The output is something like:

   ALTER TABLE [dbo].[orders] ADD [shipped_at] DATETIME2 NULL;
   GO
   CREATE INDEX [IX_orders_shipped_at] ON [dbo].[orders] ([shipped_at]);
   GO

5. Save the script as 2026-05-06-orders-shipped-at.sql, commit it to your migrations folder, and apply with Apply to Target after backup.
6. Repeat staging → production with the same script — the diff confirms staging now matches production-minus-this-change before you ship.

2. Audit drift between environments that should match

Two environments that "should" be identical often aren't. Run a compare with the source and target swapped, or just look at Modified + Removed on a single direction.

• Index drift — a hot-fix index added in production but never back-ported to dev shows up as "Removed" when source is dev.
• Permission and role drift — not in the schema compare scope; use Security Manager for that.
• Collation / engine drift — common in MySQL where one replica was rebuilt with a different default. Schema Compare flags it at the table-options level.

3. Verify a deployment landed cleanly

After running a migration on production, point Schema Compare at production as source and the migration's expected end-state (a checkout of staging or a freshly-built canary) as target. The expected outcome is "0 differences". Anything else is a deployment problem — missed migration, partial run, or a permission failure.

4. Generate a rollback script before a risky change

Before a schema change, capture the current state by comparing target vs target with no source — or simply compare the new state (post-deploy) back to the old state. The generated DDL is your rollback. Save it alongside the forward migration. This is the safest pattern when you can't easily restore from backup.

Schema Compare Tool Limitations & Edge Cases

Knowing where Schema Compare doesn't help saves time. These are the real boundaries:

• Cross-engine compares are not supported. SQL Server vs PostgreSQL, Oracle vs MySQL, etc. The grammars don't map 1:1 and a "translated" diff would be misleading. For schema porting between engines, use a hand-written migration plus the Object Explorer's Script as CREATE output as a starting point.
• Data is not compared. Schema Compare only looks at structure. Use Data Compare for row-level diffs, especially for reference / lookup tables.
• Permissions, roles, and logins are out of scope. Use Security Manager for those.
• Computed-column expressions are compared as text. Two semantically equivalent expressions that differ in whitespace or parenthesization will show as Modified. Most tools have this limitation; reformat both sides if needed.
• Personal mode caps at 200 objects per side. A 200-table schema with 50 views, 100 procedures, and 50 functions = 400 total — over the limit. Pro mode removes the cap.
• SQL Server temporal-table history is treated as a regular table. History tables show up as separate objects in the diff — safe, but can be noisy. Use the object-type filter to hide them when reviewing.

Best Practices

• Always backup your target database before applying schema changes
• Review carefully - Check the generated script for potentially destructive changes
• Test in staging before applying changes to production
• Use source control - Save generated scripts for audit trails