locked
trying to find unused tables by views,function, and proc? RRS feed

  • Question

  • I come up with lists of unused table through given script

    SELECT SCHEMA_NAME(t.schema_id) as SchemaName,
           t.name as TableName
    FROM   sys.tables t
    WHERE  is_ms_shipped = 0
           AND NOT EXISTS (SELECT *
                           FROM   sys.sql_expression_dependencies d
                           WHERE  d.referenced_entity_name = t.name
                                  AND (( is_ambiguous = 1 or is_caller_dependent=1)
                                         OR
                              d.referenced_id = t.object_id)  )

    result:

    dbo temp_bandfix
    dbo ClientParty_AXClient_back
    dbo EmployeeParty_AXEmployee_back
    dbo CUSTTABLE
    dbo sysdiagrams
    dbo ConcurClientList

    I have list of procs, Vws, and FN in DB

    I am thinking to add sub query on the above script and see dependency on procs, views and FN to make sure those tables are not used at all  in database before dropping them.

    Looking for help on this issue???

    Friday, April 25, 2014 5:45 PM

Answers

  • Use SQL Server Profiler to run a trace on your database. You can let it run for a week or longer if you'd like to. Then once it is done you can upload the results to an SQL table. Then you can use a join to the query you have above to see if there are any tables not being accessed. This is probably the best method for determining if a table is unused, as the Profiler will log events anytime someone runs a transaction against a table.


    Jordan Johnson

    • Marked as answer by Fanny Liu Monday, May 5, 2014 9:31 AM
    Friday, April 25, 2014 10:47 PM
  • Use this free tools its very important :

    http://www.red-gate.com/products/sql-development/sql-search/

    http://stackoverflow.com/questions/13563364/determine-which-objects-reference-a-table-in-sql-server

    • Marked as answer by Fanny Liu Monday, May 5, 2014 9:31 AM
    Saturday, April 26, 2014 12:04 AM

All replies

  • Search for it.  Most any question you can think of has already been addressed.  In fact, look at the "top related threads" pane for this thread. 
    Friday, April 25, 2014 5:50 PM
  • Use SQL Server Profiler to run a trace on your database. You can let it run for a week or longer if you'd like to. Then once it is done you can upload the results to an SQL table. Then you can use a join to the query you have above to see if there are any tables not being accessed. This is probably the best method for determining if a table is unused, as the Profiler will log events anytime someone runs a transaction against a table.


    Jordan Johnson

    • Marked as answer by Fanny Liu Monday, May 5, 2014 9:31 AM
    Friday, April 25, 2014 10:47 PM
  • Use this free tools its very important :

    http://www.red-gate.com/products/sql-development/sql-search/

    http://stackoverflow.com/questions/13563364/determine-which-objects-reference-a-table-in-sql-server

    • Marked as answer by Fanny Liu Monday, May 5, 2014 9:31 AM
    Saturday, April 26, 2014 12:04 AM
  • Nice query, but you should consider that sys.sql_expression_dependencies also contains dependencies for check constraints and indexes. Therefore, if you have a table that is not referenced by any stored procedure, view, function or trigger, but it has a check constraint or an index defined on it, that table won't appear in the results of the above query.
    Saturday, April 26, 2014 3:46 PM