Saturday, 11 October 2008

Finding table differences in Oracle

Often there is a need to compare two databases and see the differences. I come across this a lot when releasing a new build into an existing environment - the new code runs on the development database, but the test environment needs a schema upgrade before the code will run.

There are tools that will compare schemas for you, but sometimes I just need to quickly find out what columns are missing from the target schema (this is particularly useful when comparing different development schemas and you want to quickly implement changes - production releases need much better quality control than this).

The sql below provides one solution - showing the columns that are missing from 'owner1' when compared to 'owner2' for tables that match the prefix:

This will ofcourse identify missing tables. It's primative, but its an easy way to see basic structural differences using SQL - without the need for expensive tools. Since it is basic sql, you could include this as part of an environment verification test suite, or include in your application startup procedure - providing you have access to a reference schema for comparison.


Post a Comment