Script to Compare Database Structures

I know I CAN hack something together but I hate to reinvent the wheel.

I want to be able to compare the structure of two different clients
databases that might be on different servers that are firewalled away
from each other. Given the two structures it will list all the SQL
commands needed to make the database structure the same.

In a perfect world on one side you would pull up a PHP page that does
a "generate structure" which would create a downloadable file which
you could then upload to the other system which would then give a
listing of the SQL commands needed to make the local structure match
the uploaded structure.

Thanks in advance...

Matt

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Script to Compare Database Structures

--0015175cfb861465f604700c7112
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

have you tried mysqldiff?

++Tim Hinnerk Heuer++

http://www.ihostnz.com (should be .org)

Sponsors welcome to put ads under a linked to page. This is not automated
just yet. Only image, swf or preferably text or short html ads, no
animations please. Price negotiable.


2009/8/1 Matt Neimeyer

> I know I CAN hack something together but I hate to reinvent the wheel.
>
> I want to be able to compare the structure of two different clients
> databases that might be on different servers that are firewalled away
> from each other. Given the two structures it will list all the SQL
> commands needed to make the database structure the same.
>
> In a perfect world on one side you would pull up a PHP page that does
> a "generate structure" which would create a downloadable file which
> you could then upload to the other system which would then give a
> listing of the SQL commands needed to make the local structure match
> the uploaded structure.
>
> Thanks in advance...
>
> Matt
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--0015175cfb861465f604700c7112--

Re: Script to Compare Database Structures

On Fri, Jul 31, 2009 at 05:38:44PM -0400, Matt Neimeyer wrote:

> I know I CAN hack something together but I hate to reinvent the wheel.
>
> I want to be able to compare the structure of two different clients
> databases that might be on different servers that are firewalled away
> from each other. Given the two structures it will list all the SQL
> commands needed to make the database structure the same.
>
> In a perfect world on one side you would pull up a PHP page that does
> a "generate structure" which would create a downloadable file which
> you could then upload to the other system which would then give a
> listing of the SQL commands needed to make the local structure match
> the uploaded structure.
>
> Thanks in advance...

I don't know what flavor of SQL you're using, but for SQL-compliant
databases, there is an "information_schema" table (I believe that's the
correct name) which contains most or all of the data you need. Query
that table into an array for each server, and compare the arrays.

Here's a query I've used:

SELECT table_name, column_name, data_type, column_default, is_nullable,
character_maximum_length, numeric_precision, numeric_scale FROM
information_schema.columns WHERE table_name = 'tablename' ORDER BY
ordinal_position

This will not tell you the relations between tables, nor which columns
are primary keys, etc. There may be another way to get this out of
information_schema.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Script to Compare Database Structures

Look into the Reverse module of PEAR MDB2.
http://pear.php.net/manual/en/package.database.mdb2.intro-re verse-module.php

Cheers,

Mattias

Matt Neimeyer wrote:
> I know I CAN hack something together but I hate to reinvent the wheel.
>
> I want to be able to compare the structure of two different clients
> databases that might be on different servers that are firewalled away
> from each other. Given the two structures it will list all the SQL
> commands needed to make the database structure the same.
>
> In a perfect world on one side you would pull up a PHP page that does
> a "generate structure" which would create a downloadable file which
> you could then upload to the other system which would then give a
> listing of the SQL commands needed to make the local structure match
> the uploaded structure.
>
> Thanks in advance...
>
> Matt
>
>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Script to Compare Database Structures

I finally got a chance to play with this and it looks like it is
exactly what I need.

Thanks!

On Fri, Jul 31, 2009 at 11:50 PM, German Geek wrote:
> have you tried mysqldiff?
>> I want to be able to compare the structure of two different clients
>> databases that might be on different servers that are firewalled away
>> from each other. Given the two structures it will list all the SQL
>> commands needed to make the database structure the same.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php