Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Querying the INFORMATION_SCHEMA Expand / Collapse
Author
Message
Posted Tuesday, March 24, 2009 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 10:01 PM
Points: 14, Visits: 71
Hi Mike,

I enjoyed the article that I found as I was searching for something similar. We are trying to clean up a database where the dba stored every ad hoc report he was asked to create as a stored procedure. I was hoping to find a way to query the INFORMATION_SCHEMA to see what stored procedures had not been used in the last month or more and be able to target them as possible candidates for deletion. Is that possible?
Thanks,
Tim "DBA jr."

Post #682907
Posted Tuesday, March 31, 2009 6:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
Tim:

Sorry for the late response. Hopefully by now you have found the solution. The question does raise an eyebrow. Basically, when was the last time a given sproc was fired. I know there are thrid party tools that monitor a database for activity - a table structure was modified, a sprc changed, etc. I do not have an immediate answer.
Post #686960
Posted Tuesday, March 31, 2009 5:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:31 PM
Points: 21, Visits: 187
Hi Timothy,

monitoring 'execution count' for a while might help.
See this article by Gregory Larsen -
http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm



Post #687569
Posted Thursday, September 29, 2011 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:38 AM
Points: 7, Visits: 377
INFORMATION_SCHEMA.ROUTINES does not return triggers data, look here: [url=http://msdn.microsoft.com/en-us/library/ms188757.aspx][/url]

ROUTINE_TYPE
nvarchar(20)
Returns PROCEDURE for stored procedures, and FUNCTION for functions.
Post #1183010
Posted Monday, June 25, 2012 11:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193

WOW

I have not seen this article in a long time. IT is pretty cool to see that it is still out there.
Post #1320797
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse