#! /bin/sybperl
#
# @(#)dbschema.pl 1.11 2/22/94
#
#
# dbschema.pl A script to extract a database structure from
# a Sybase database
#
# Written by: Michael Peppler (mpeppler@itf.ch)
# Last Modified: 22 Feb 1994
#
# Usage: dbschema.pl -d database -o script.name -t pattern -s server -v
# where database is self-explanatory (default: master)
# script.name is the output file (default: script.isql)
# pattern is the pattern of object names (in sysobjects)
# that we will look at (default: %), and server is
# the server to connect to (default, the value of $ENV{DSQUERY}).
#
# -v turns on a verbose switch.
#
# Changes: 11/18/93 - bpapp - Put in interactive SA password prompt
# 11/18/93 - bpapp - Get protection information for views and
# stored procedures.
# 02/22/94 - mpeppler - Merge bpapp's changes with itf version
# 06-07/96 - dpartridge - Added disk inits, thresholds, user info,
# etc.
#------------------------------------------------------------------------------
require 'sybperl.pl';
require 'getopts.pl';
require 'ctime.pl';
require 'sql.pl';
@nul = ('not null','null');
select(STDOUT); $| = 1; # make unbuffered
do Getopts('d:t:o:p:s:v');
$opt_d = "";
$opt_t = '%' unless $opt_t;
$opt_s = $ENV{DSQUERY} unless $opt_s;
$opt_p = $ENV{SAPW} unless $opt_p;
$opt_o = "$opt_s.$opt_d.schema.sql" unless $opt_o;
$need_d_comma = 0;
$need_l_comma = 0;
$opt_d = $db;
$dumpdir = $ENV{DUMPDIR};
$DIOUTFILE = "$dumpdir/$opt_s.disk.init.sql";
#
# Log us in to Sybase as 'sa' and prompt for admin password.
#
if ($opt_p eq "" || $opt_p == 1) {
print "\nAdministrative account password: ";
system("stty -echo");
chop($sapw = <>);
system("stty echo"); }
else {
$sapw = $opt_p;
}
$dbproc = &dblogin("sa", $sapw, $opt_s);
&diskinit();
&sql($dbproc, "use master");
@dbs = &sql($dbproc, "select name from sysdatabases order by name");
foreach $n (@dbs) {
# print $n;
# print "\n";
&getSchema($n);
}
&dbexit;
sub getSchema
{
local($db) = $_[0];
$OUTFILE = "$dumpdir/$opt_s.$db.schema.sql";
print "Producing output file: $OUTFILE\n";
open(SCRIPT, "> $OUTFILE") || die "Can't open $OUTFILE: $!\n";
open(LOG, "> $OUTFILE.log") || die "Can't open $OUTFILE.log: $!\n";
print SCRIPT
"/* This Isql script was generated by dbschema.pl on $date.
** The indexes need to be checked: column names & index names
** might be truncated!
*/\n";
#
# Query system tables for database layout, produce create statement - dap, 6/96
#
&dbcmd($dbproc, "select device_fragments = v.name, size =");
&dbcmd($dbproc, " str(size / 512, 10, 1),");
&dbcmd($dbproc, " usage = m.description");
&dbcmd($dbproc, " from master.dbo.sysdatabases d,");
&dbcmd($dbproc, " master.dbo.sysusages u,");
&dbcmd($dbproc, " master.dbo.sysdevices v,");
&dbcmd($dbproc, " master.dbo.spt_values a,");
&dbcmd($dbproc, " master.dbo.spt_values b,");
&dbcmd($dbproc, " master.dbo.sysmessages m");
&dbcmd($dbproc, " where d.dbid = u.dbid");
&dbcmd($dbproc, " and v.low <= u.size + vstart");
&dbcmd($dbproc, " and v.high >= u.size + vstart - 1");
&dbcmd($dbproc, " and v.status & 2 = 2 ");
&dbcmd($dbproc, " and d.name = '$db'");
&dbcmd($dbproc, " and a.type = 'E'");
&dbcmd($dbproc, " and a.number = 1");
&dbcmd($dbproc, " and b.type = 'S'");
&dbcmd($dbproc, " and u.segmap & 7 = b.number");
&dbcmd($dbproc, " and b.msgnum = m.error");
&dbcmd($dbproc, " and isnull(m.langid, 0) = 0");
&dbcmd($dbproc, " order by 3,1");
&dbsqlexec($dbproc);
&dbresults($dbproc);
print "Database create statement ...";
print SCRIPT "create database $db on ";
while((@dat = &dbnextrow($dbproc)))
{
$x = join('~', @dat);
$x =~ s/ //g;
$x =~ s/\.0//;
($devname, $devsize, $devtype ) = split("~", $x);
if ($devtype =~ /dataonly|dataandlog/)
{
if ($need_d_comma == 0)
{
$need_d_comma = 1;
print SCRIPT "$devname = $devsize";
}
else
{
print SCRIPT ", $devname = $devsize";
}
}
else
{
if ($need_l_comma == 0)
{
$need_l_comma = 1;
print SCRIPT " log on $devname = $devsize";
}
else
{
print SCRIPT ", $devname = $devsize";
}
}
}
print SCRIPT "\ngo\n";
print "Done\n";
$need_d_comma = 0;
$need_l_comma = 0;
&sql($dbproc, "use $db");
chop($date = &ctime(time));
print LOG "Error log from dbschema.pl on Database $db on $date\n\n";
print LOG "The following objects cannot be reliably created from the script in $opt_o.
Please correct the script to remove any inconsistencies.\n\n";
print SCRIPT "\nuse $db\ngo\n"; # Change to the appropriate database
#
# Add the thresholds - dap, 7/96
#
&getThresholds($db);
# Add the users/groups - dap, 6/96
#
&getusers();
# Add the appropriate user data types:
#
print "Add user-defined data types...";
print SCRIPT
"/* Add user-defined data types: */\n\n";
&dbcmd($dbproc, "select s.length, s.name, st.name,\n");
&dbcmd($dbproc, " object_name(s.tdefault),\n");
&dbcmd($dbproc, " object_name(s.domain)\n");
&dbcmd($dbproc, "from $db.dbo.systypes s, $db.dbo.systypes st\n");
&dbcmd($dbproc, "where st.type = s.type\n");
&dbcmd($dbproc, "and s.usertype > 100 and st.usertype < 100 and st.usertype != 18\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{
print SCRIPT "sp_addtype $dat[1],";
if ($dat[2] =~ /char|binary/)
{
print SCRIPT "'$dat[2]($dat[0])'";
}
else
{
print SCRIPT "$dat[2]";
}
print SCRIPT "\ngo\n";
# Now remeber the default & rule for later.
# print "Adding rule $dat[4] for $dat[1]\n" if $opt_v;
# print "Adding default $dat[3] for $dat[1]\n" if $opt_v;
$urule{$dat[1]} = $dat[4] if $dat[4] !~ /NULL/;
$udflt{$dat[1]} = $dat[3] if $dat[3] !~ /NULL/;
}
print "Done\n";
print "Create rules...";
print SCRIPT
"\n/* Now we add the rules... */\n\n";
&getObj('Rule', 'R');
print "Done\n";
print "Create defaults...";
print SCRIPT
"\n/* Now we add the defaults... */\n\n";
&getObj('Default', 'D');
print "Done\n";
print "Bind rules & defaults to user data types...";
print SCRIPT "/* Bind rules & defaults to user data types... */\n\n";
while(($dat, $dflt)=each(%udflt))
{
# print "sp_bindefault $dflt, $dat\ngo\n" if $opt_v;
print SCRIPT "sp_bindefault $dflt, $dat\ngo\n";
}
while(($dat, $rule) = each(%urule))
{
# print "sp_bindrule $rule, $dat\ngo\n" if $opt_v;
print SCRIPT "sp_bindrule $rule, $dat\ngo\n";
}
print "Done\n";
print "Create Tables & Indices...";
print "\n" if $opt_v;
&dbcmd($dbproc, "select o.name,u.name, o.id\n");
&dbcmd($dbproc, "from $db.dbo.sysobjects o, $db.dbo.sysusers u\n");
&dbcmd($dbproc, "where o.type = 'U' and o.name like '$opt_t' and u.uid = o.uid\n");
&dbcmd($dbproc, "order by o.name\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{
# print "Adding object: $dat[0], user: $dat[1]\n" if $opt_v;
$_ = join('@', @dat); # join the data together on a line
push(@tables,$_); # and save it in a list
}
foreach (@tables) # For each line in the list
{
@tab = split(/@/, $_);
# print "Creating table $tab[0], owner $tab[1] in database $db\n" if $opt_v;
print SCRIPT "/* Start of description of table $tab[1].$tab[0] */\n\n";
&dbcmd($dbproc, "select Column_name = c.name, \n");
&dbcmd($dbproc, " Type = t.name, \n");
&dbcmd($dbproc, " Length = c.length, \n");
&dbcmd($dbproc, " Nulls = convert(bit, (c.status & 8)),\n");
&dbcmd($dbproc, " Default_name = object_name(c.cdefault),\n");
&dbcmd($dbproc, " Rule_name = object_name(c.domain)\n");
&dbcmd($dbproc, "from $db.dbo.syscolumns c, $db.dbo.systypes t\n");
&dbcmd($dbproc, "where c.id = $tab[2]\n");
&dbcmd($dbproc, "and c.usertype *= t.usertype\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
undef(%rule);
undef(%dflt);
print SCRIPT "\n\nCREATE TABLE $db.$tab[1].$tab[0]\n (";
$first = 1;
while((@field = &dbnextrow($dbproc)))
{
print SCRIPT ",\n" if !$first; # add a , and a \n if not first field in table
print SCRIPT "\t$field[0] \t$field[1]";
print SCRIPT "($field[2])" if $field[1] =~ /char|bin/;
print SCRIPT " $nul[$field[3]]";
$rule{"$tab[0].$field[0]"} = $field[5] if ($field[5] !~ /NULL/ && $urule{$field[1]} ne $field[5]);
$dflt{"$tab[0].$field[0]"} = $field[4] if ($field[4] !~ /NULL/ && $udflt{$field[1]} ne $field[4]);;
$first = 0 if $first;
}
print SCRIPT " )\n";
print SCRIPT "go\n";
# now get the indexes...
#
print "Indexes for table $tab[1].$tab[0]\n" if $opt_v;
&dbcmd($dbproc, "sp_helpindex '$tab[1].$tab[0]'\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@field = &dbnextrow($dbproc)))
{
print SCRIPT "\nCREATE ";
print SCRIPT "unique " if $field[1] =~ /unique/;
print SCRIPT "clustered " if $field[1] =~ /^clust/;
print SCRIPT "index $field[0]\n";
@col = split(/,/,$field[2]);
print SCRIPT "on $db.$tab[1].$tab[0] (";
$first = 1;
foreach (@col)
{
print SCRIPT ", " if !$first;
$first = 0;
print SCRIPT "$_";
}
print SCRIPT ")\n";
print SCRIPT "go\n";
}
&getPerms("$tab[1].$tab[0]");
print SCRIPT "go\n";
# print "Bind rules & defaults to columns...\n" if $opt_v;
print SCRIPT "/* Bind rules & defaults to columns... */\n\n";
if($tab[1] ne 'dbo' && (keys(%dflt) || keys(%rule)))
{
print SCRIPT "/* The owner of the table is $tab[1].
** I can't bind the rules/defaults to a table of which I am not the owner.
** The procedures below will have to be run manualy by user $tab[1].
*/";
print LOG "Defaults/Rules for $tab[1].$tab[0] could not be bound\n";
}
while(($dat, $dflt)=each(%dflt))
{
print SCRIPT "/* " if $tab[1] ne 'dbo';
print SCRIPT "sp_bindefault $dflt, '$dat'";
if($tab[1] ne 'dbo')
{
print SCRIPT " */\n";
}
else
{
print SCRIPT "\ngo\n";
}
}
while(($dat, $rule) = each(%rule))
{
print SCRIPT "/* " if $tab[1] ne 'dbo';
print SCRIPT "sp_bindrule $rule, '$dat'";
if($tab[1] ne 'dbo')
{
print SCRIPT " */\n";
}
else
{
print SCRIPT "\ngo\n";
}
}
print SCRIPT "\n/* End of description of table $tab[1].$tab[0] */\n";
}
print "Done\n";
#
# Now create any views that might exist
#
print "Create views...";
print SCRIPT
"\n/* Now we add the views... */\n\n";
&getObj('View', 'V');
print "Done\n";
#
# Now create any stored procs that might exist
#
print "Create stored procs...";
print SCRIPT
"\n/* Now we add the stored procedures... */\n\n";
&getObj('Stored Proc', 'P');
print "Done\n";
#
# Now create the triggers
#
print "Create triggers...";
print SCRIPT
"\n/* Now we add the triggers... */\n\n";
&getObj('Trigger', 'TR');
print "Done\n";
print "\nLooks like I'm all done!\n";
close(SCRIPT);
close(LOG);
splice(@tables,0);
splice(@rule,0);
splice(@dflt,0);
splice(@dat,0);
#print "Undefining default associative array ..,\n" if $opt_v;
undef %udflt;
#print "Undefining rule associative array ..,\n" if $opt_v;
undef %urule;
}
sub getPerms
{
local($obj) = $_[0];
local($ret, @dat, $act, $cnt);
&dbcmd($dbproc, "sp_helprotect '$obj'\n");
&dbsqlexec($dbproc);
$cnt = 0;
while(($ret = &dbresults($dbproc)) != $NO_MORE_RESULTS && $ret != $FAIL)
{
while(@dat = &dbnextrow($dbproc))
{
$act = 'to';
$act = 'from' if $dat[2] =~ /Revoke/;
print SCRIPT "$dat[2] $dat[3] on $obj $act $dat[1]\n";
# print SCRIPT "$dat[0] $dat[1] on $obj $act $dat[2]\n";
++$cnt;
}
}
$cnt;
}
sub getObj
{
local($objname, $obj) = @_;
local(@dat, @items, @vi, $found);
# print "Executing getObj for types $objname, $obj ...\n" if $opt_v;
# print "Data ...\n" if $opt_v;
&dbcmd($dbproc, "select o.name, u.name, o.id\n");
&dbcmd($dbproc, "from $opt_d.dbo.sysobjects o, $opt_d.dbo.sysusers u\n");
&dbcmd($dbproc, "where o.type = '$obj' and o.name like '$opt_t' and u.uid = o.uid\n");
&dbcmd($dbproc, "order by o.name\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{ #
$_ = join('@', @dat); # join the data together on a line
push(@items, $_); # and save it in a list
print "$dat[0], $dat[1]\n" if $opt_v;
}
foreach (@items)
{
@vi = split(/@/, $_);
$found = 0;
&dbcmd($dbproc, "select text from syscomments where id = $vi[2]");
&dbsqlexec($dbproc);
&dbresults($dbproc);
print SCRIPT
"/* $objname $vi[0], owner $vi[1] */\n";
while(($text) = &dbnextrow($dbproc))
{
if(!$found && $vi[1] ne 'dbo')
{
++$found if($text =~ /$vi[1]/);
}
print SCRIPT $text;
}
print SCRIPT "\ngo\n";
if(!$found && $vi[1] ne 'dbo')
{
print "**Warning**\n$objname $vi[0] has owner $vi[1]\nbut this is not mentioned in the CREATE PROC statement!!\n";
print LOG "$objname $vi[0] (owner $vi[1])\n";
}
if ($obj eq 'V' || $obj eq 'P')
{
&getPerms("$vi[0]") && print SCRIPT "go\n";
}
}
}
sub getusers
{
local(@group, @user, @grp, @usr, @alias, @als);
#find out if the dbowner is someone other than dbo, sa
&dbcmd($dbproc, "select a.name from master.dbo.syslogins a, sysusers b ");
&dbcmd($dbproc, "where a.suid=b.suid ");
&dbcmd($dbproc, "and b.uid = 1 ");
&dbcmd($dbproc, "and a.suid != 1 ");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while(($dat = &dbnextrow($dbproc)))
{ #
print "dbowner is $dat\n" if $opt_v;
print SCRIPT
"\n/* Add db owner */\n\n";
print SCRIPT "sp_changedbowner $dat\ngo\n";
}
&dbcmd($dbproc, "sp_helpgroup");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{ #
$_ = join('@', @dat); # join the data together on a line
push(@group, $_); # and save it in a list
print "$dat[0]\n" if $opt_v;
}
print SCRIPT
"\n/* Add groups */\n\n";
foreach (@group)
{
@grp = split(/@/, $_);
print
"adding group $grp[0] ...\n" if $opt_v;
print SCRIPT
"sp_addgroup $grp[0]\ngo\n";
}
&dbcmd($dbproc, "sp_helpuser\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{ #
$_ = join('@', @dat); # join the data together on a line
push(@user, $_); # and save it in a list
print "$dat[0]\n" if $opt_v;
}
print SCRIPT
"\n/* Add users */\n\n";
foreach (@user)
{
@usr = split(/@/, $_);
print
"adding user $usr[0] to group $usr[2] ...\n" if $opt_v;
if ($usr[0] !=~ /dbo/) {
if ($usr[2] =~ /public/){
print SCRIPT
"sp_adduser $usr[0], $usr[3]\ngo\n\n"; }
else {
print SCRIPT
"sp_adduser $usr[0], $usr[3], $usr[2]\ngo\n\n"; }
}
}
&dbcmd($dbproc, "select b.name, a.name from sysalternates c, sysusers a, master.dbo.syslogins b\n");
&dbcmd($dbproc, "where a.suid = c.altsuid and b.suid = c.suid\n");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{ #
$_ = join('@', @dat); # join the data together on a line
push(@alias, $_); # and save it in a list
print "$dat[0]\n" if $opt_v;
}
print SCRIPT
"\n/* Add alias users */\n\n";
foreach (@alias)
{
@als = split(/@/, $_);
print
"aliasing login $als[0] to $als[1] ...\n" if $opt_v;
print SCRIPT
"sp_addalias $als[0], $als[1]\ngo\n\n";
}
}
sub getThresholds
{
local($db) = $_[0];
local(@threshold, @thrsh);
&dbcmd($dbproc, "sp_helpthreshold");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{ #
$_ = join('@', @dat); # join the data together on a line
push(@threshold, $_); # and save it in a list
print "$dat[0]\n" if $opt_v;
}
print "Adding thresholds...";
print SCRIPT
"\n/* Add thresholds */\n\n";
foreach (@threshold)
{
@thrsh = split(/@/, $_);
print SCRIPT
"sp_addthreshold $db, $thrsh[0], $thrsh[1], $thrsh[3]\ngo\n";
}
print "Done\n";
}
#
# Get the device info and produce disk init statements - dap, 6/96
#
sub diskinit
{
open(DISCRIPT, "> $DIOUTFILE") || die "Can't open $DIOUTFILE: $!\n";
print "Producing disk init statements ...";
&dbcmd($dbproc, " select device_name = v.name,");
&dbcmd($dbproc, " physical_name = v.phyname,");
&dbcmd($dbproc, " device_number = convert(tinyint, substring(convert(binary(4), v.low),");
&dbcmd($dbproc, " a.low, 1)),");
&dbcmd($dbproc, " cntrltype, pages = (v.high - v.low + 1) ");
&dbcmd($dbproc, " from master.dbo.sysdevices v,");
&dbcmd($dbproc, " master.dbo.spt_values a");
&dbcmd($dbproc, " where a.type = 'E' and a.number = 3");
&dbsqlexec($dbproc);
&dbresults($dbproc);
while((@dat = &dbnextrow($dbproc)))
{
$x = join('~', @dat);
$x =~ s/ //g;
($devname, $physname, $devno, $ctrltype, $pages ) = split("~", $x);
if ($ctrltype =~ /0/)
{
print DISCRIPT "disk init\nname = \"$devname\",\n";
print DISCRIPT "physname = \"$physname\",\n";
print DISCRIPT "vdevno = $devno,\n";
print DISCRIPT "size = $pages,\n";
print DISCRIPT "cntrltype = $ctrltype\n";
print DISCRIPT "go\n\n";
}
}
print "Done\n";
close(DISCRIPT);
}