Generate DDL for Sybase Instance

generate_ddl.sh

#!/bin/bash

sql="select name,type from dbo.sysobjects where type in ('P', 'R', 'U', 'V') and uid = 1 order by type,name\nGO"
sql_file="list_tables.sql"
tables_file="tables.txt"
ddl_dir="ddl"
tar_file="database.tar.gz"
server="some_server"
user="some_user"
password="some_password"
database="some_database"

# ensure this script is run by the sybase user
[ $USER != "sybase" ] && echo "Script must be run as 'sybase' user" && exit 1

# create list_tables.sql if it does not exist already
[ ! -a $sql_file ] && echo -e $sql > $sql_file

# create ddl directory if it does not exist already
[ ! -d $ddl_dir ] && mkdir -p $ddl_dir/P $ddl_dir/R $ddl_dir/U $ddl_dir/V

# get the list of tables (removing the file first)
[ -a $tables_file ] && rm $tables_file
isql -U$user -S$server -P$password -D$database -b -i$sql_file -o$tables_file

# remove the last two lines from the tables file (which are blank and show number of rows selected)
head -n -2 $tables_file > $tables_file-new && mv $tables_file-new $tables_file

# perform ddl generation
cat $tables_file | while read name type
do
  /opt/sybase/ASEP/bin/ddlgen -S$server -U$user -P$password -D$database -N$name -T$type -o$ddl_dir/$type/$name\.sql > /dev/null
done

# tar the output (removing the file first)
[ -a $tar_file ] && rm $tar_file
tar -zcf $tar_file $ddl_dir