Table compare

A great feature about HeidiSQL is that multiple servers / databases can easily be accessed from the tree on the left.

I use this to maintain three 'identical structured' environments:
- development environment (DEV)
- quality assurance / test environment (PQA)
- live environment (LIVE)

The only difference between the environments is the data that the tables contain.

Often, I made changes in the dev environment without documenting it in the conversion script. In that way I afterwards have to search which changes I made.

In my opinion a great HeidiSQL would be a 'table-structure-compare', that searches for changes between two tables, or more, maybe even between two entire databases.

The step after this compare feature would be a button to generate a update script ;-)

That's a complex feature you are asking for. You know there are diff tools which can compare text files, e.g. your SQL dump from dev with the one from live. Beyond Compare is excellent for this task, I already used it to compare .sql files. However, that's surely a work around. You should probably starr issue #1289.

Thanks for the Google Code issueIt doesn't seem that active, but it indeed is mentioned before

Certainly, the feature must be really difficult to get right: I've evaluated many database compare tools and they all suck (no exceptions).

The compare part is easy anyway: you just need to get normalized SQL dumps of both databases (mysqldump or even HeidiSQL should be enough) and compare them with your favourite file compare tool (I use WinMerge but there're many to choose from).

What HeidiSQL supports currently is doing a SQL export directly onto another server. Doing that, you can tell HeidiSQL to drop tables before recreating them - probably that helps a bit. Of course this ends up in a full sync, not some incremental sync.

Dropping tables in the production database to import development data seems not to be a very good idea to me.

I also work w/ different databases of equal structure (1 dev. and a few prod. DBs). Copying the ALTER TABLE statements from Heidi's SQL log into a changelog (w/ short comments) is not that hard.
"Copy line to new query tab" (context menu @ SQL log) is a neat feature: after doing so, just use another DB and alter it's table(s), too.

Just because of that great feature that creates CREATE TABLE statements, in my opinion it would be great to add a simple comparisson feature.

It is even possible to select the fields of the selected table, and compare it with a field with the same fieldname of the other table. Don't you all think?

possible: yes
quick hack: no
needful: NO

I insist: comparing databases is quite trivial. As I explained, you can already compare two databases with HeidiSQL and a regular file compare tool.

The difficult part is creating the appropriate SQL statements to port changes. That's the key feature: without it, you need to duplicate manually any change you've detected. And no tool I'm aware of does it right, so I doubt HeidiSQL will succeed where everyone else fails (no offense, Anse). By adding half cooked features you can't rely on we won't have a better tool.

May I suggest a halfway solution that seems (at least to me) somewhat simpler to implement?

The idea would be for HeidiSQL to keep track of all DDL statements (CREATE, DROP, ALTER and such) since connection start, separated per database, and have them displayed/exported on request - either as a SQL file or copy-pastable text in a query tab.

This would allow to easily replicate on a deployed server any modification that has been tested and found working on a test server first (which is more or less the way a lot of people develops web applications - playing first on a localhost copy of the app and porting things over to the "real" one once it seems safe and sound).

That sounds like a good intermediate sollution, but the problem with that is that it only works if only one developper is working with that database ;-)

Doesn't it sound like a great plan to make a start at comparing field-types within tables? After that, the complete comparrison features will automatically follow

Synchronizing table structure seems quite a difficult task and I don't know if it's possible to make it reliable in all cases. Sometimes the changes are obvious - like a new column is added or deleted. But what if a column is renamed? How would a program know that the developer really renamed the column? Maybe he deleted the old column and created a new one with the new name? This leads to critically different ALTER TABLE statements. The same could apply to renaming tables, indexes and foreign keys. Without tracking what the user does there would need to be some manual intervention in the process.

A pretty easy solution that would be handy would be an option to select objects (databases, tables, etc.) for comparison and heidi would make CREATE statements for these objects and send them to an external diff tool like TortoiseDiff or some other user-defined tool. This should be easy to do and would provide an easy way to compare structures.

I think the goal isn't the synchronising, the goal should be a function that shows you where the differences are. Thereafter, the user has to alter the tables itself.

Selecting the objects yourself (as lemon_juice mentions) indeed is a good option

I see a synchronize function has been made. Does it fit to the requirements as described in this issue? In that case that should be really great, and I want to thank the devvers for that

Please stick to issue #1289 so we're not spreading discussions all over the internet.

Everybody says there's no good table compare tool in any application. What about Toad? It has compare and more.

First post, can't insert link yet: https :// blog. toadworld. com/2017/02/14/compare-and-sync-multiple-tables-with-toad