compare two database schema

[earlrainer]

hi guys,

is there any way I can compare two database.

I have two databases and they were supposed to be identical, but i find that one of them is having tables not present in the other, same case with triggers etc.

does sybase have any utility that helps me compare the structure of the two databases?

 

[markmar1]

I do not know of a Sybase specific tool. I think Embarcadero may have tools.

I usually do some simple type of things. Compare contents of sysobjects for some glaring missing objects.

Another quick and simple way may be to reverse engineer via 'ddlgen', and then do a comparison between the two environments. Kinda of crude, but can be effective. I've done the comparison pretty quickly on Pc using WinMerge that gives nice visual of differences. Of course, if segments, etc are different, thos would be pointed out quickly....

Just some quick thoughts

 

[sybaseguru]

As suggested the best bet is loading both databases on the same server (assuming that you can find enough space to create both next to each other). Then do a compare of sysobjects and look at the ones which do not exist or have a different "crdate" value in sysobjects.

For table comparison, I normally use the following script. You may find it useful and adapt it to your needs.

You need to pass the server name and the name of two databases as shell parameters.

#!/bin/ksh
#--------------------------------------------------------------------------------
#
# Procedure: analyse.ksh
#
# Description: compare tables and column changes in two # databases
#
#--------------------------------------------------------------------------------
# Vers| Date | Who | DA | Description
#-----+--------+-----+----+-----------------------------------------------------
# 1.0 |06/10/00| MT | | Initial Version
#-----+--------+-----+----+-----------------------------------------------------
#
SQL_SERVER_USED=$1
DB_OLD=$2
DB_NEW=$3

if [[ -z ${SQL_SERVER_USED} ]]
then
send_alert "Abort: $0 failed. No server name specified on command line"
exit 1
fi

if [[ -z ${DB_OLD} ]]
then
send_alert "Abort: $0 failed. No old database name specified on command line"
exit 1
fi
if [[ -z ${DB_NEW} ]]
then
send_alert "Abort: $0 failed. No new database name specified on command line"
exit 1
fi

ENVFILE=$HOME/${SQL_SERVER_USED}/dba/bin/environment.ksh
if [[ -f $ENVFILE ]]
then
. $ENVFILE
else
echo "Abort: $0 failed. No environment file ( $ENVFILE ) found"
exit 1
fi

USERNAME="sa"; export USERNAME
FILE_NAME=`basename $0 .ksh`
#
PASSWORD=$(~/dba/bin/get_password.ksh $SQL_SERVER_USED $USERNAME)
if [ $? != 0 ]
then
echo "Abort: $0 failed. Query for Sybase password failed"
exit 1
#
fi
LOG_FILE="${LOGDIR}/${SQL_SERVER_USED}_${FILE_NAME}_${DATABASE}.log"
[ -f ${LOG_FILE} ] && rm -f ${LOG_FILE}
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER_USED} -w1000 << ! > ${LOG_FILE}
use ${DB_NEW}
go
if exists(select 1 from tempdb..sysobjects where name = &quot;new_tables&quot; and type = &quot;U&quot
drop table tempdb..new_tables
go
create table tempdb..new_tables ( name varchar(30) not null )
go
insert tempdb..new_tables
select v5.name from ${DB_NEW}..sysobjects v5 where v5.type = 'U'
and not exists (select 1 from ${DB_OLD}..sysobjects o where o.type = 'U' and v5.name = o.name)
go
select 'list of new tables'
select ''
select name from tempdb..new_tables
order by name
go
use ${DB_OLD}
go
if exists(select 1 from tempdb..sysobjects where name = &quot;dropped_tables&quot; and type = &quot;U&quot
drop table tempdb..dropped_tables
go
create table tempdb..dropped_tables ( name varchar(30) not null )
go
insert tempdb..dropped_tables
select o.name from ${DB_OLD}..sysobjects o where o.type = 'U'
and not exists (select 1 from ${DB_NEW}..sysobjects v5 where v5.type = 'U' and o.name = v5.name)
go
select 'list of dropped tables'
select ''
select name from tempdb..dropped_tables
order by name
go
exit
!
#
### now bcp out tempdb..new_tables and tempdb..dropped_tables
#
for t in tempdb..new_tables tempdb..dropped_tables
do
echo &quot; Unloading ${t} ...&quot;
bcp ${t} out ${LOGDIR}/${t}.bcp -c -S${SQL_SERVER_USED} -U${USERNAME} -P${PASSWORD}
done
#
### Now check which columns their type etc have changed for tables
#
for DATABASE in ${DB_OLD} ${DB_NEW}
do
TEMP_FILE=&quot;${LOGDIR}/${DATABASE}_t_and_c.tmp&quot;
[ -f ${TEMP_FILE} ] && rm -f ${TEMP_FILE}
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER_USED} -w1000 -X -h << ! > ${TEMP_FILE}
use ${DATABASE}
go
set nocount on
go
if exists(select 1 from tempdb..sysobjects where name = &quot;${DATABASE}_t_and_c&quot; and type = &quot;U&quot
drop table tempdb..${DATABASE}_t_and_c
go
SELECT name = so.name,
column = c.name,
type = t.name,
length = c.length
into tempdb..${DATABASE}_t_and_c
FROM sysobjects so,
syscolumns c,
systypes t
WHERE so.id = c.id
AND so.type = 'U'
AND c.usertype = t.usertype
go
SELECT header1 = so.name,
header2 = c.name,
header3 = t.name,
header4 = c.length
FROM sysobjects so,
syscolumns c,
systypes t
WHERE so.id = c.id
AND so.type = 'U'
AND c.usertype = t.usertype
order by so.name, c.colid
go
exit
!
cat ${TEMP_FILE} | egrep -i -v 'row affected|rows affected|header1|--'|sed -e '/^$/d' > temp.sql
mv temp.sql ${TEMP_FILE}
done
DIFF_FILE=&quot;${LOGDIR}/t_and_c.diff&quot;
[ -f ${DIFF_FILE} ] && rm -f ${DIFF_FILE}
diff -b ${LOGDIR}/${DB_OLD}_t_and_c.tmp ${LOGDIR}/${DB_NEW}_t_and_c.tmp > ${DIFF_FILE}
cat ${DIFF_FILE} | egrep '<|>' > temp.sql
mv temp.sql ${DIFF_FILE}
#
COLUMN_CHANGES_FILE=&quot;${LOGDIR}/columns_changes.list&quot;
[ -f ${COLUMN_CHANGES_FILE} ] && rm -f ${COLUMN_CHANGES_FILE}
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER_USED} -w1000 -X -h << ! > ${COLUMN_CHANGES_FILE}
use tempdb
go
select 'column changes'
go
--select a.name,a.column, &quot;new type &quot; = a.type, &quot;old type &quot; = b.type
select distinct a.name
from tempdb..${DB_NEW}_t_and_c a,
tempdb..${DB_OLD}_t_and_c b
where a.name = b.name
and a.column = b.column
and a.type <> b.type
--order by a.name, a.column
order by a.name
go
exit
!
NEW_COLUMNS_FILE=&quot;${LOGDIR}/new_columns.list&quot;
[ -f ${NEW_COLUMNS_FILE} ] && rm -f ${NEW_COLUMNS_FILE}
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER_USED} -w1000 -X -h << ! > ${NEW_COLUMNS_FILE}
use tempdb
go
select 'new columns'
go
--select a.name,a.column
select distinct a.name
from tempdb..${DB_NEW}_t_and_c a
where not exists (select 1 from tempdb..${DB_OLD}_t_and_c b
where a.name = b.name
and a.column = b.column
)
--order by a.name, a.column
order by a.name
go
exit
!
REMOVED_COLUMNS_FILE=&quot;${LOGDIR}/removed_columns.list&quot;
[ -f ${REMOVED_COLUMNS_FILE} ] && rm -f ${REMOVED_COLUMNS_FILE}
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER_USED} -w1000 -X -h << ! > ${REMOVED_COLUMNS_FILE}
use tempdb
go
select 'removed columns'
go
--select a.name,a.column
select distinct a.name
from tempdb..${DB_OLD}_t_and_c a,
tempdb..${DB_NEW}_t_and_c b
where a.name = b.name -- table is the same but column removed
and not exists (select 1 from tempdb..${DB_NEW}_t_and_c c
where a.column = c.column
)
--order by a.name, a.column
order by a.name
go
exit
!