Tracking down unused Views and Stored Procedures

Question

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends.

This database needs a lot of love and attention which I am attempting to give it.

There are lots of stored procedures and views in this database, some are used, some are not, some are linked to Forms or Reports in Access, some are run directly in Access.

To make this project more manageable I would like to drop anything that is historical and not used any more. All the code is in source control so I can restore anything if needed.

What ways are there to log which stored procedures and views are used?

For the stored procedures I can add an INSERT INTO dbo.log with the name of the stored procedure to every stored procedure, but I can't think of a way to do this with the views.

Answer 1

1. For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat.

To find unused stored procs, please see the answer as explained by Aaron Bertrand.

Also, there is an good read to identify as explained here in the article How to find unused Stored Procedures in SQL Server 2005/2008

2. To tack down unused views:

a) Generate a List of non-recently-used code (by querying the plan cache).

b) Script the Views/Sprocs/etc that aren’t in cache (for whatever reason) using ALTER scripts. (i.e. the SSMS ‘Modify’ option when right-clicking on an object).

c) Execute the ALTER script generated by SSMS in production. Since the object wasn’t in cache, there’s no harm and no foul in worrying about recompilation overhead. And since the script is an ALTER (not a DROP/CREATE), permissions are retained intact.

d) But, if the object references invalid objects, it’ll throw an error and I can definitively know that it’s not only NOT used, but one of the culprits that’s causing me grief during replication snapshot procedures.

Also read as mentioned by Jonnathan@ here

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. Since you want a method that works across 2000-2012 your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event: