Any takers from the 0 people who read this blog?
Toad has a nice tool, but Toad is of course expensive. I have a shell script I hacked together a while back to solve this problem. Its oracle specific, but could be easily modified to work on another database.
In a nutshell, it talks to sqlplus to get a list of tables, dumps the schema ('desc' output) to a file. It does this for each database, then just uses 'diff' to diff the files.
#!/bin/bash -x
if [ $# -ne 2 ]; then
echo "Usage: $0 [SCHEMA1] [SCHEMA2]"
exit 1;
fi
SCHEMA1=$1
SCHEMA2=$2
echo "set heading off;" > /tmp/tables.sql
echo "select table_name from tabs;" >> /tmp/tables.sql
echo "quit;" >> /tmp/tables.sql
function dump() {
mkdir -p /tmp/$1/tables
sqlplus -S $2 @/tmp/tables.sql | grep -v "rows selected" | sort -u >
/tmp/$1/tables.out
for table in `cat /tmp/$1/tables.out`; do
#echo $table
echo "set heading off;" > /tmp/$1/tables/$table.sql
echo "desc $table;" >> /tmp/$1/tables/$table.sql
echo "quit;" >> /tmp/$1/tables/$table.sql
sqlplus -S $2 @/tmp/$1/tables/$table.sql | grep -v "rows selected" |
grep -v " Name" | grep -v -e "-----" | sort > /tmp/$1/tables/$table.out
done
}
dump one $SCHEMA1
dump two $SCHEMA2
diff -r /tmp/one /tmp/two
<< Home