Stored Procedure Statistics

[tcorrigan]

Is there any way in SQL 7.0 to find out how often a stored procedure has been run? I could clearly do this myself but before I go to the effort I want to find out if maybe SQL Server is doing it for me already.
[sig][/sig]

 

[christine1]

There are several ways to check this. What I did was create a status table that would record getdate() Time started and Time completed of the sproc being run. I can refer to that table and check what has run, when and how long it took to run.

If you are speaking historically, try checking the task scheduler if the sproc's are automated and there's always the error and transaction logs.

Here's the code for the table:

set nocount on */this is optional/*

DECLARE @start_time datetime
@end_time datetime
@s_time datetime
@e_time datetime
SELECT @start_time = getdate()

select @s_time = getdate()
EXEC sp_run_sproc */ this is your own sproc/*
select e_time = getdate()

I hope it works, let me know.

[sig][/sig]