I'd like to find a tool that can detect redundant/unused tables and stored procedures in a sybase database.
The DB is accessed from a Java application which uses JDBC to access both the tables and the stored procs.
There are neraly 400 tables and a similar number of stored procedures.
Any suggestions?
Assuming that the table names are all hard-coded within the application and not dynamically constructed, you could do a search of the java code and stored procedures looking for the names 400 tables. Of the ones that didn't show up, you'd need to verify that there weren't FK references from a used table before considering them as candidates for dropping. Most database client tools (ex. DBVisualizer) would allow you to look for these on a table by table basis. There may even be a way to issue this as a query, but I'm not familiar enough with Sybase to say for sure.
The only sure fire way to see what the app isn't using is to setup logging and run through a full exercise of the application. This can be done from the client and/or server.
You can log the JDBC activity with log4jdbc or You can also have the DBA setup a trace on that spid.
And a good DBA aught to have a job scheduled in production to gather periodic snapshots to see what's happening, this may help you depending on whats being monitored.
After you've identified candidates for removal, you can create an ObjectLog Table and script out triggers on the tables and call in each proc to insert into that table. Running that in production for a while will give you the confidence to drop away.
Another approach, assuming you're using ASE 12.5+, is to use the MDA tables. One of these tables, monOpenObjectActivity I think, keeps track of all object usage. You can run queries against this table to find how frequently indexes, tables and sprocs are used. If you can find your way around it it's exceptionally useful.
The first step is to install the tables, as they're not there by default. The best guide is here: http://www.sypron.nl/mda.html. Also on that page are some sample queries, and I think the one titled "find seemingly unused indexes in the current database" is the one for you. This query can easily be adapted to report on all usage of all objects.