How to find which SP are not being used by ASP Classic application

Aleks
Aleks used Ask the Experts™
on
We have an old database with around 400 stored procedures.
The stored procedures are called from an ASP Classic application.

Is there a way to know reliably which of the stored procedures are not being used by the application?
Or do we need to search for the stored procedure name on all the ASP pages in the application in order to find those that don't have any match?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
If you're sure they are only called from the ASP application, then I would search through each file.  There are plenty of utilities that can help you with this, or you can write a quick script using FIND to retrieve every line in every file with each stored procedure.

Author

Commented:
Yeah I figured. I can use Visual Studio Code I believe. I wanted to avoid having to type each SP name to search for them.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
Change into the root directory of your ASP site and run
for /f "tokens=*" %a in ('dir /b /s *.asp') do @find /i "exec myStoredProcedure" %a

where "myStoredProcedure" is the name of one of your procedures.  

If you create a list of procedures, you can likely create a small batch file nesting for statements and run it once to do all procedures, saving the results to a txt file by the name of the procedure.  For example:

@echo off
for /f "tokens=*" %%m in (myListOfProcedures.txt) do (
   for /f "tokens=*" %%a in ('dir /b /s *.asp') do find /i "exec %%m" %%a >>%%m-results.txt
)

Open in new window

Technology and Business Process Advisor
Most Valuable Expert 2013
Commented:
So dump a list of stored procedures and run the script against it.
Big MontyWeb Ninja at large

Commented:
I've actually run into this scenario myself, unfortunately there's no way to do it and be 100% accurate. You can check certain system tables and see when the last time a SP has run, but that ONLY works if it's in cache, as soon as it's dropped from the system cache, it'll disappear out of the table. Cache is usually emptied whenever the sql server agent restarts.

This should at least get you some of the SP's, but always double check!

select b.name, a.last_execution_time, *
from sys.dm_exec_procedure_stats a 
inner join sys.objects b on a.object_id = b.object_id 
where DB_NAME(a.database_ID) = 'your database name'

Open in new window


source - https://ask.sqlservercentral.com/questions/1948/can-i-tell-when-a-stored-procedure-was-last-execut.html

the *right* way to do it is follow Lee's suggestion, and check the source code,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial