Is there a way to quickly compare the table structures of the tables
of two databases and see what's different?

For example to see if the staging server's db has an extra column in
some table that the live server doesn't?

I'm asking in reference to mysql and/or postgre, thanks,

m

<dterrors (AT) hotmail (DOT) com> wrote

Don't know about postgre or mysql, but here's the usual way to do this:

Create a "metadatabase". It's a database whose user tables reflect the
metadata stored in other databases. This is pretty easy to do, because you
are copying (some of) the content of the system tables in the target
database. You may need to add a column to indicate which database a given
item came from. You may have to change the table and column names a little,
depending on the quirks of your DBMS, but not much.

Query the system tables in the target database, and use the results to load
the tables in your metadatabase.

now, comparing columns to make sure they exist in both (or all three, or
all four, etc.) is as simple as this:

select table_name, column_name
from columns
group by table_name, column_name
having count(1) < expected_value

This is only one example of hundreds of queries that can be made on the
metadatabase.

MySQL has the INFORMATION_SCHEMA database which provides a view into
the metadata tables. A query to give all columns that exist in the '
staging_db' that don't exist in 'live_db' would look something like
the following (note that I haven't tested query!):

SELECT a.TABLE_NAME, a.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.table_name = b.table_name
WHERE a.table_schema = 'staging_db'
AND b.table_schema = 'live_db'
AND b.column_name IS NULL
;

-Tri
http://architechsolutions.com

On Mar 4, 9:43 pm, dterr... (AT) hotmail (DOT) com wrote:

MySQL has the INFORMATION_SCHEMA database which provides a view into
the metadata tables. A query to give all columns that exist in the '
staging_db' that don't exist in 'live_db' would look something like
the following (note that I haven't tested the query!):

SELECT a.TABLE_NAME, a.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.table_name = b.table_name
AND a.column_name=b.column_name
WHERE a.table_schema = 'staging_db'
AND b.table_schema = 'live_db'
AND b.column_name IS NULL
;


-Tri
http://architechsolutions.com


On Mar 4, 9:43 pm, dterr... (AT) hotmail (DOT) com wrote: