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?
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?
Lee W, MVP
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.
ASKER
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.
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:
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
)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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,
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'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,