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:
SELECT table_name, column_name, data_type FROM all_tab_cols
WHERE lower(owner)=lower(:owner1)
AND table_name LIKE :prefix
AND column_name NOT LIKE 'SYS%'
AND table_name||'-'||column_name NOT IN
(SELECT table_name||'-'||column_name FROM all_tab_cols
WHERE lower(owner)=lower(:owner2)
)
ORDER BY table_name, column_name;
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.
