SQL is a standard. right! well mostly a standard.

lately I have been looking at how to diff two different databases. you know get what is the difference between the two. first we look at does the two databases (DB1 and DB2 for this post) have the same tables. first just start with does the two have the same table names. then compare the column names of each table in DB1 to DB2, then vice versa. at this point we have the same table schema between the two. then we look at each table to see if the data is the same. first look at row counts (that is simple). then compare row by row. and for very large tables (large in number of rows and number of columns) this can be time consuming.

sounds easy enough. wrong!

the problem is with the table schemas, the schemas arent going to be the same between different database engines (SQLite, PostgreSQL, MySQL, MariaDB, Oracle, Microsoft SQL Server, cubeSQL, Valentina, etc). what might be an integer on one, might be bigint on another. so comparing the two databases if they are the same database engine is fairly simple (or more simple). if the engines are different then holy mackerel the mappings of what should be what is chaotic at best. then some database engines have column types of UUID and others dont know what that is. so how to you map those?

now trying to compare column types between all those would make a DBA drink. and drink a lot.

–headmonkey