How to detect if a database structure changed? (not content!)

Question

In my php script I want to check if a structure of database table has changed. The DESCRIBE TABLE wont provide enough informations, because it ignores the foreign keys. The SHOW CREATE TABLE is too much, because auto incremental value also appears here.

Look at the information_schema.* tables. I think some combination of it's data will probably work well (columns, table_constraints & triggers perhaps). — Wrikken
– Wrikken, Commented Apr 25, 2012 at 14:36

Karoly Horvath · Accepted Answer · 2012-04-25 14:34:57Z

5

The SHOW CREATE TABLE is too much, because auto incremental value also appears here.

Just filter out the auto incremental value with a regexp.

preg_replace('/ AUTO_INCREMENT=[0-9]+ /', ' ', $sql);

edited Apr 25, 2012 at 14:34

answered Apr 25, 2012 at 14:28

Karoly Horvath

96.7k11 gold badges123 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Smith Over a year ago

if you could include that regexp function please

ypercubeᵀᴹ Over a year ago

Nice. (minor drawback is that it will show false negative if an index or a foreign key has been dropped and recreated with different name)

Karoly Horvath Over a year ago

good point! depends on what you want to detect.. you could filter out that as well

Collectives™ on Stack Overflow

How to detect if a database structure changed? (not content!)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related