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

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?
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
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.
0
AleksAuthor 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.
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
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

0
Lee W, MVPTechnology and Business Process AdvisorCommented:
So dump a list of stored procedures and run the script against it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big MontyWeb Ninja at largeCommented:
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,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.