locked
How to find unused tables RRS feed

  • Question

  • How to find unused tables and store procedures in a database in SQL Server 2005
    Thursday, December 3, 2009 8:46 AM

Answers

  • For tables, you would have to take a snapshot of the database (backup db, restore it as copyofdb) and do a compare a month later for example with a tool like SQL Data Compare.

    Unused stored procedure check is tough.

    You may do this:

    1. Have DBA monitor specific sprocs with SQL Server Profiler for a week
    2. If monitored sprocs do not show up in trace, rename them
    3. Delete renamed sprocs 3 months later
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Wednesday, December 9, 2009 8:28 AM
    Thursday, December 3, 2009 9:01 AM

All replies

  • If you have SQL profiler traces for that period , you may look in to it and see whether or not the objects were accessed .
    Thanks, Leks
    Thursday, December 3, 2009 8:57 AM
  • For tables, you would have to take a snapshot of the database (backup db, restore it as copyofdb) and do a compare a month later for example with a tool like SQL Data Compare.

    Unused stored procedure check is tough.

    You may do this:

    1. Have DBA monitor specific sprocs with SQL Server Profiler for a week
    2. If monitored sprocs do not show up in trace, rename them
    3. Delete renamed sprocs 3 months later
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Wednesday, December 9, 2009 8:28 AM
    Thursday, December 3, 2009 9:01 AM
    • Proposed as answer by Naomi N Thursday, December 3, 2009 2:30 PM
    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 7:43 PM
    • Unmarked as answer by Kalman Toth Tuesday, April 17, 2012 7:44 PM
    Thursday, December 3, 2009 9:52 AM