Re: Stored Routines
Spyros/Russell
My apologies. I just pulled the file using Samba from my Unix box to my NT
desktop and did not realize that the Unix <LF> is not converted to MS-?
<CR><LF> on the fly, so it may confuse non-unix users. I am enclosing the
file as inlined plaintext for all.
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<--------------
----------------------------------------------------------
Spyros Macris <spyros@phillyc.com> on 08/19/99 05:56:30 PM
To: Sujit Pal
cc:
Subject: Re: Stored Routines
How do you open the spdeps.pl file? I am running Windows NT.
Russell
I have a perl script which does this. Its kind of a brute force
approach,
but it gives a list of column and table dependencies for all your stored
procedures. I plan to write a more selective tool later when I get some
time. Here it is as a plaintext attachment.
HTH
Sujit
(See attached file: spdeps.pl)