My situation has some similarities: I'm also working on Sybase 11.x databases at a client, where I maintain the data model as well as write a good deal of the back-end SQL code. I have a few cool tools which I wrote for fun and life simplification. You may want to give them a shot.

Sometimes I need to know if anyone's made changes to a database schema, and if so, what they are. I wrote a tool I called schemadiff that pulls definitions of tables, indices, and views from two databases named on the command line and prints a list of the differences. I do the diff in Perl so I can control the output format. (To see how to read the Sybase catalogs, look at the text of procs like sp_help, sp_helpindex, and so on, with sp_helptext or defncopy. All the system procs are in the sybsystemprocs database.) In your case, you may want to write something that compares the text of stored procedures in your DEV and TST databases rather than relying on the text stored in files. As a matter of fact, you could generalize it so you can compare the text in a file with the one stored in the database.

Using the same definition-mining code (I really should make it a module ;-) I wrote schemarep, which does a nicely formatted listing of the database's contents. Just some fun with Perl formats.

I had to write a series of maintenance routines for about a dozen tables with similar structures. Rather than writing a dozen similar procs, I wrote one Perl script in which I described the tables and had it cook up and execute three SQL scripts per table on the fly. That is, the code looks a little like this:

my @colgroup1 = qw(foo bar baz);
my @colgroup2 = qw(oop ack);
# ...
my %tables = (
    table1 =>
    {
        KEYS => [qw(colx coly)],
        DATA => [ @colgroup1 ],
    },
    table2 =>
    {
        KEYS => [ qw(cola colb) ],
        DATA => [ @colgroup2 ],
    },
# ....
};
# ...
foreach my $tabname (keys %tables)
{
    my $buddy  = $tabname . '_work';
    my $tabref = $tables{$tabname};
    $sql = qq{
        delete $tabname
        from $tabname t
        where not exists(
            select 1 from $buddy where
        };
    $sql .=
        join("\n and ", 
            map { "t.$_ = w.$_" }
                @{$tabref->{KEYS}};
    $sql .= "\n)";
# ...
[download]

In case you're wondering, that last bit of SQL deletes rows from one table where the primary key is not present in another table with the same primary key (the "buddy table"). (I can't post the actual code since my contract with my client stipulates that anything I write here has to stay here. Otherwise I would have posted it to CUFP. But you get the idea.)

Heh. I'll be watching this thread myself for more cool ideas....

HTH

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.