How to Compare Two Database Schemas with SQL

Somebody added a column to the dev database six weeks ago. The migration script that was supposed to carry it to production exists, sort of, in a branch someone means to merge. The deploy goes out, the queries start failing in ways that look like application bugs, and by the time anyone thinks to check the actual tables, the afternoon is gone.

Schema drift is boring right up until it isn't. Here's how to answer "are these two databases actually the same?" with plain SQL — queries you can run in any client, including the free tier of mine, against any engine you like.

The idea: make the schema describe itself

Every relational database keeps its own catalog — a set of system tables that list your tables, columns, and types. Query the catalog on both databases, export the results, and diff the two lists. Same output means same shape. Different output means you've found your missing column before it found you.

The queries below list every table with its columns and types. That's 90% of drift in practice: columns added, renamed, or retyped.

PostgreSQL

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

MySQL / MariaDB

SELECT table_name, column_name, column_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'your_database_name'
ORDER BY table_name, ordinal_position;

SQL Server

SELECT t.name AS table_name, c.name AS column_name,
       ty.name AS data_type, c.is_nullable
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
ORDER BY t.name, c.column_id;

SQLite

SQLite keeps it honest and simple. List the tables, then inspect each one:

SELECT name, sql FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

PRAGMA table_info(your_table);

The sqlite_master.sql column is the actual CREATE TABLE statement, which makes diffing two SQLite files almost pleasant: dump both, compare text.

Then diff the outputs

Run the query against database A, export the grid to CSV. Run it against database B, export again. Then:

diff staging-schema.csv production-schema.csv

or paste both into any diff tool. What you're looking for:

• Lines only in one file — a table or column that exists on one side and not the other. The classic.
• Same column, different type — integer on staging, bigint on production, because somebody fixed an overflow on one side and forgot the other.
• Nullability mismatches — the sneaky one. Everything works until production data hits the column staging made optional.

Where the simple version stops

The columns query doesn't cover indexes, constraints, views, or routines, and those drift too. You can extend the same trick — information_schema.table_constraints and information_schema.statistics-style views on Postgres and MySQL, sys.indexes on SQL Server — but be honest with yourself about what you're building: a schema compare tool, one catalog view at a time, in a .sql file nobody will maintain past March.

The other honest limit is cross-engine comparison. Comparing a Postgres schema to a MySQL one works, but the types spell different names for the same thing, so the diff fills with noise that means nothing. Same-engine comparisons are where this approach is genuinely good.

Prevention beats detection, and this is the one sermon I'll allow myself: if your schema changes only ever reach production through migration files (Flyway, Liquibase, Rails migrations, whatever fits your stack), drift mostly stops happening. The queries above are the smoke detector. Migrations are not smoking in bed.

The part where I'm straight with you

Everything above works in any client, anywhere, for free. It's worth knowing regardless of what tools you own, because it teaches you where databases keep their own secrets.

It's also the slow way if you do it every week. So I'll be honest about the shape of the market, mine included: dedicated schema compare tools exist, and SQLly has one. Ours compares any two connections — they don't even have to be the same engine — covers tables, views, indexes, foreign keys, constraints, routines and triggers, detects renames instead of showing you a drop-and-create, grades every difference by risk so a data-losing change can't hide in a pile of cosmetic ones, and generates a dependency-ordered deploy script plus a matching rollback script. Both scripts open in an editor tab for review; nothing runs until you've read it. The details are in the Schema Compare docs.

So which should you use? The SQL queries, when you're in someone else's client, on a server over SSH, or doing this twice a year. A dedicated compare, when staging and production drift is a recurring character in your life. Migrations discipline, always, so you need both less.

Try it on your own pair

• Open the live preview in your browser — the real workbench, sample databases, no account — or download SQLly.
• Save the catalog query for your engine as a snippet.
• Run it against staging, run it against production, diff the exports.

Do it once before your next deploy. The five minutes are free, and the column you catch is yours to keep.

FAQ

Can I compare schemas across two different engines, like Postgres to MySQL? Yes, with caveats. The catalog queries work on both sides, but type names differ between engines, so expect false positives in the diff. It works well for "did the table and column names carry over," less well for type-level detail.

How often should I check for drift? Before any deploy that touches the database, and after any incident that smells like a query problem but isn't. If you're running migrations properly, a monthly sanity check is enough. If you're not, run it weekly and consider this article's sermon section.

Does this catch index and constraint differences? Not the basic hand-rolled version. Extend the queries to the constraint and index catalog views for your engine — same technique, more system tables — or use a compare tool that covers them; SQLly's Schema Compare reads tables, views, indexes, foreign keys, constraints, routines and triggers in one pass.

What about views, functions, and stored procedures? Same trick, different catalog views (information_schema.views, information_schema.routines, or the engine equivalents). They drift less often than tables but hurt more when they do.