Is there any way to see when a stored procedure was last called or run? I'd like to clean up a database that was set up before I took over and I need to see which sp haven't been called in a while. Also, if it is possible, is this info stored in the master or in a system table in the db that the sp resides in?

Thanks,
Rayne Bair

--
Want to know what I read?
http://raynebair.bookcrossing.com

Rayne,
You are out of luck here. This type of information is not tracked by SQL. You could add a simple insert statement to the start of each SP that inserts a record into a simple table. Like


CREATE TABLE [dbo].[Track_SP] (
[SP_Name] [varchar] (128) NOT NULL ,
[CallDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Track_SP] ADD
CONSTRAINT [DF_Track_SP_CallDate] DEFAULT (getdate()) FOR [CallDate]
GO


-- Start Insert at start of SP
INSERT INTO Track_SP(SP_Name) VALUES ('MYSPNAme')
-- End Insert at start of SP


After about a month this would give you a good idea.


--
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction program
http://www.ag-software.com. Free programming tools

"Rayne Bair" <themrs (AT) wifetalks (DOT) com> wrote

Is there any way to see when a stored procedure was last called or run? I'd like to clean up a database that was set up before I took over and I need to see which sp haven't been called in a while. Also, if it is possible, is this info stored in the master or in a system table in the db that the sp resides in?

Thanks,
Rayne Bair

--
Want to know what I read?
http://raynebair.bookcrossing.com