Compare SQL Schemas
How to compare two SQL schemas: export each schema as DDL (mysqldump --no-data, pg_dump --schema-only, or .schema in sqlite3), paste both outputs into SQL Diff Tool, and review the highlighted CREATE TABLE, column, index and constraint changes. For live databases, pgAdmin Schema Diff or SSMS Schema Compare can also generate migration scripts.
Compare two schema dumps
Export, paste, review
- Export each schema as DDL. Run the command that matches the database, once for each side you want to compare.
MySQL
mysqldump --no-data --skip-comments --skip-dump-date \ -u user -p dbname > schema.sql
PostgreSQL
pg_dump --schema-only --no-comments \ -d dbname > schema.sql
SQLite
sqlite3 database.db .schema > schema.sql
- Paste both outputs into the SQL Diff Tool. You can use the panes on this page or the same tool on the home page. Turn on “Ignore whitespace” if the two dumps were formatted differently.
- Review the highlighted CREATE TABLE, column, index and constraint changes. Removed lines show on the left and added lines on the right.
pgAdmin Schema Diff and SSMS Schema Compare are the better fit when you are already connected to the live servers and want a migration script. This comparison shows the DDL differences; it does not write the ALTER statements.
Frequently asked questions
How do I compare two SQL schemas?+
Export each schema as DDL, paste the two outputs into SQL Diff Tool, and read the highlighted lines. CREATE TABLE statements, columns, indexes and constraints that differ show up as added or removed lines.
Which command exports a schema without the data?+
MySQL uses mysqldump --no-data. PostgreSQL uses pg_dump --schema-only. SQLite uses .schema in the sqlite3 shell. Run the command once per database so you have two DDL files to compare.
Can this generate the migration between two live databases?+
No. This page compares DDL text and shows what changed. pgAdmin Schema Diff and SSMS Schema Compare connect to live databases and can generate a migration script when that is what you need.