Re: Perl program to check column dependencies for a SP
David
I mangled the email address the last time when I cc-ed you on Jesse's mail.
Sujit
---------------------- Forwarded by Sujit Pal on 09/26/2000 09:57 AM
---------------------------
From: Sujit Pal on 09/26/2000 09:25 AM
To: Jessie Aquino <Jessie_Aquino@ama-assn.org>
cc: informxi-list@iiug.org
Subject: Re: Perl program to check column dependencies for a SP (Document
link: Database 'Sujit Pal', View '($Sent)')
Jessie
Yes, you are right, the code is incomplete, thanks for pointing it out. Here is
the complete code (see below).
David,
could you please update the page referred to below? The update in question is
for question 8.50. HTML is probably getting confused by the < and > signs even
with <pre>..</pre> tags. I use < and > instead.
Sorry for posting to the whole group, but I could not find David William's email
address anywhere on the site.
Thanks
Sujit
---------------------------------------------------------------8<------------------------------------------------------------
#!/usr/local/bin/perl
#
# Routine to output a complete list of table and column dependencies
# for a stored procedure.
#
if ($#ARGV != 0)
{
die "Usage: ", $0, " database_name\\n";
}
$dbname = $ARGV[0];
print "Table and Column Dependencies for Stored Procedures in Database ",
$dbname, "\\n\\n";
#
# Find all table names
#
@tabnames = `dbaccess $dbname - 2>/dev/null <<EOF
SELECT tabname FROM systables
WHERE tabid > 99
ORDER BY tabnameEOF
`;
#
# Find all unique column names
#
@colnames = `dbaccess $dbname - 2>/dev/null <<EOF
SELECT UNIQUE colname FROM syscolumns
WHERE tabid > 99
ORDER BY colnameEOF
`;
#
# Find all stored procedure names
#
@procnames = `dbaccess $dbname - 2>/dev/null <<EOF
SELECT procname FROM sysprocedures
WHERE mode = "O"
ORDER BY procnameEOF
`;
$i = 0;
foreach (@procnames)
{
chop;
$i++;
if ($i <= 4)
{
next;
}
if ($_ eq '')
{
last;
}
$_ =~ s/ //g;
$procname = $_;
#
# Get stored procedure body into array
#
@procbodys = `dbschema -d $dbname -f $procname`;
$i = 0;
foreach (@procbodys)
{
chop;
$i++;
if ($i < 5)
{
next;
}
$procline = lc($_);
#
# Find table dependencies
#
for ($j = 4; $j <= $#tabnames; $j++)
{
$tabnames[$j] =~ s/ //g;
$tabname = lc($tabnames[$j]);
chop($tabname);
if (index($procline, $tabname) > 0)
{
if (index($taboutput{$procname}, $tabname) <= -1)
{
$taboutput{$procname} .= $tabname;
$taboutput{$procname} .= " ";
last;
}
}
}
#
# Find column dependencies
#
for ($j = 4; $j <= $#colnames; $j++)
{
$colnames[$j] =~ s/ //g;
$colname = lc($colnames[$j]);
chop($colname);
if (index($procline, $colname) > 0)
{
if (index($coloutput{$procname}, $colname) <= -1)
{
$coloutput{$procname} .= $colname;
$coloutput{$procname} .= " ";
last;
}
}
}
}
}
#
# Print the report
#
print "Table Dependencies\\n";
print "------------------\\n";
#foreach $keys (sort {$taboutput{$a} <=> $taboutput{$b}} keys %taboutput))
foreach $keys (sort(keys %taboutput))
{
print $keys, ": ", $taboutput{$keys}, "\\n";
}
print "\\nColumn Dependencies\\n";
print "------------------\\n";
foreach $keys (sort(keys %coloutput))
{
print $keys, ": ", $coloutput{$keys}, "\\n";
}
---------------------------------------------------------------8<------------------------------------------------------------
Jessie Aquino <Jessie_Aquino@ama-assn.org> on 09/26/2000 06:58:15 AM
To: Sujit Pal@BankofAmerica
cc:
Subject: Perl program to check column dependencies for a SP
I found your perl program posted at this site
http://www.smooth1.demon.co.uk/ifaq08.htm and seems that there are some missing
code. I very interested to use you program please vist the site and if you could
please send me a working copy.
Thanks
Jessie Aquino
AMA