<!-- --><style type="text/css">@import url(https://www.blogger.com/static/v1/v-css/navbar/3334278262-classic.css); div.b-mobile {display:none;} </style> </head><body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d7256432\x26blogName\x3dThe+Frustrated+Programmer\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLACK\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://frustratedprogrammer.blogspot.com/search\x26blogLocale\x3den_US\x26v\x3d2\x26homepageUrl\x3dhttp://frustratedprogrammer.blogspot.com/\x26vt\x3d5012862196962223429', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>
| Tuesday, July 06, 2004

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