MSSQL Database Cleanup - How do you find unused objects (Tables, Views, Procs, Functions)

Question

Lets say you have inherited a MS SQL 2000 or 2005 database, and you know that some of the Tables, Views, Procs, and Functions are not actually used in the final product.

Is there some kind of internal logging or another mechanism that could tell me what objects are NOT being called? or have only been called a few times versus thousands of times.

Answer 1

The answer will depend a little on how the database has been put together, but my approach to a similar problem was 3-fold:

Figure out which objects have no internal dependencies. You can work this out from queries against sysdepends such as:

select 
    id, 
    name
from
    sys.sysdepends sd
inner join sys.sysobjects so
    on so.id = sd.id
where 
    not exists (
     select 
      1
     from 
      sysdepends sd2
     where 
      sd2.depid = so.id
    )

You should combine this with collecting the type of object (sysobjects.xtype) as you'll only want to isolate the tables, functions, stored procs and views. Also ignore any procedures starting "sp_", unless people have been creating procedures with those names for your application!

Many of the returned procedures may be your application's entry points. That is to say the procedures that are called from your application layer or from some other remote call and don't have any objects that depend on them within the database.

Assuming the process won't be too invasive (it will create some additional load, though not too much) you can now switch on some profiling of the SP:Starting, SQL:BatchStarting and / or SP:StmtStarting events. Run this for as long as you see fit, ideally logging into a sql table for easy cross referencing. You should be able to eliminate many of the procedures that are called directly from your application.

By cross referencing the text data from this log and your dependent object list you will hopefully have isolated most of the unused procedures.

Finally, you may want to take your candidate list resulting from this process and grep your sourcecode base against them. This is a cumbersome task and just because you find references in your code doesn't mean you need them! It may simply be that the code hasn't been removed though it's now logically inaccessible.

This is far from a perfect process. A relatively clean alternative is to set up far more detailed (and therefore invasive) profiling on the server to monitor all the activity. This can include every SQL statement called during the time the log is active. You can then work back through the dependent tables or even cross-database dependencies from this text data. I've found the reliability of the log detail (too many rows per second attempting to be parsed) and the sheer quanitity of data difficult to deal with. If your application is less likely to suffer from this then it may be a good approach.

Caveat:

Because, so far as I'm aware, there isn't a perfect answer to this be particularly wary of removing tables. Procedures, functions and views are easily replaced if something goes wrong (though make sure you have them in source control before burning them of course!). If you're feeling really nervous why not rename the table and create a view with the old name, you've then got an easy out.

Answer 2

This SO question, Identifying Unused Objects In Microsoft SQL Server 2005, might be relevant.