Querying the INFORMATION

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mike DiRenzo » Querying the INFORMATION_SCHEMA

25 posts, Page 3 of 3

«««1 2 3

Querying the INFORMATION_SCHEMA

Rate Topic

Display Mode

Topic Options

Author

Message

Timothy Lenz

Posted Tuesday, March 24, 2009 3:42 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 01, 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

Mike DiRenzo

Posted Tuesday, March 31, 2009 6:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143, Visits: 183

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

adrian_rf

Posted Tuesday, March 31, 2009 5:39 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 10:54 PM
Points: 21, Visits: 176

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

dalexmailbox

Posted Thursday, September 29, 2011 2:59 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 7, Visits: 337

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

Mike DiRenzo

Posted Monday, June 25, 2012 11:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143, Visits: 183

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 »

25 posts, Page 3 of 3

«««1 2 3

Permissions