How to identify stored procedures, views and tables not accessed/called in the past month?

Posted on 2010-01-05
Last Modified: 2012-05-08
Objective: To clean up database by removing unused stored procedures, tables and views.

Question: How can I identify what stored procedures, tables and views that have NOT been used in the last month?

Environment: SQL Server 2005
Question by:happyCoder
    LVL 75

    Accepted Solution

    there is no easy way you need to check the frontend codes and the stored procedures for sp calls ..
    sql server stores only the recently executed statetments in its cache.
    also you can run the sql profiler to find out the currently executing stored procedures
    LVL 60

    Assisted Solution

    there is no good way to do this.  you'd have to either run a trace for a month to capture the procedures that have been executed or you'd have to write logic into your procs to record when they're ran.  No other way to do this.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now