How to check when a stored procedure was last called/executed 1

[mobajwa]

Hi
Our SQL server has a lot of stored procedures and we want to get some cleaning up to be done. We want to delete the ones that have been not run for like 2-3 months. How exactly will i find out which ones to delete. Enterprise manager only seesm to give the "Create Date"
How exactly can I find the last called date ! I guess you could write a query for that ! but how ???

 

[donutman]

Using only 3-months as the criteria for that is rather dangerous. What I like to do is rename the SP from spName to DeletespName. Then wait a long time to see what breaks.
Something else that really helps is having a documentation table that lists every user object on the server and provides a column for the programmer to comment on it's purpose and program that uses it. That table is automatically updated every night with a schedule job run against the sysobjects table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

There is a refdate in the sysobjects table (use information_Schema or a view to see it), but I'm not sure if it actually updates itself.

I agree with Karl, though. Using a date reference isn't a good way to get rid of something because there is always going to be a tiny bit of code hiding out there in exception land that will execute that stored procedure as soon as you delete it. At least by changing the name and leaving it that way for a while, you won't lose the procedure and its associated security id.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mobajwa]

Thanx for the reply guys.

"What I like to do is rename the SP from spName to DeletespName."

Thats a great idea, but where do i start of with ! how exactly will i fiure out which ones i should rename first !



secondly can you discuss a little more detail about the other idea.

"Something else that really helps is having a documentation table that lists every user object on the server and provides a column for the programmer to comment on it's purpose and program that uses it. That table is automatically updated every night with a schedule job run against the sysobjects table."

Would that table update everytime the stored procedure runs or will it just have detail of what uses the stored procedure.

 

[donutman]

That Documentation table I spoke of is a custom application that I wrote. I have a SP that runs each night to make sure that any new SPs, tables, views that I create are at least added to this table. It also keeps track of objects that are deleted (by an Update instead of Insert). Then I have a VB application that I run from time to time which checks for undocumented objects. The program allows me to update the table with the necessary comments, etc.
Here's how you can list the objects (my db is 'Deerfields'):

[Blue]SELECT[/Blue]  [red]'Deerfields'[/red] DatabaseName[Gray],[/Gray] [Blue]CASE[/Blue] xtype 
        [Blue]WHEN[/Blue] [red]'P'[/red] [Blue]THEN[/Blue] [red]'SP'[/red] [Blue]WHEN[/Blue] [red]'U'[/red] [Blue]THEN[/Blue] [red]'Table'[/red] 
        [Blue]WHEN[/Blue] [red]'V'[/red] [Blue]THEN[/Blue] [red]'View'[/red] [Blue]END[/Blue] ObjectType[Gray],[/Gray] 
        [Name]  ObjectName
   [Blue]FROM[/Blue] Deerfields.dbo.sysobjects
   [Blue]WHERE[/Blue] xtype [Blue]IN[/Blue] [Gray]([/Gray][red]'P'[/red][Gray],[/Gray] [red]'U'[/red][Gray],[/Gray] [red]'V'[/red][Gray])[/Gray] [Gray]AND[/Gray] status [Gray]>[/Gray][Gray]=[/Gray] 0

Check out Catadmin's idea with refdate and see if that helps. Otherwise getting started on an object documentation application is a good way to narrow down the list of unused objects. It's also usefull for other reasons.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mobajwa]

thanx for the piece of code Donutman.
it gives me all the objects in there, but this is only a good solution if done from the start which we didnt do !
Now i can start making the table and everything from today , but i will still have to wait a month or 2 if i can go back to see what stored procedures are used or not.
Any other solution which i could use to start getting ri of stored procedures right away !!!


Lastly,, i ran this code and gave me a bunch of dates.. what are these dates for ?

SELECT refdate
FROM dbo.sysobjects