Is there a "Code Coverage" equivalent for SQL databases?

Question

I have a database with many tables that get used, and many tables that are no longer used. While I could sort through each table manually to see if they are still in use, that would be a cumbersome task. Is there any software/hidden feature that can be used on a SQL Server/Oracle database that would return information like "Tables x,y,z have not been used in the past month" "Tables a,b,c have been used 17 times today"? Or possibly a way to sort tables by "Date Last Modified/Selected From"?

Or is there a better way to go about doing this? Thanks

edit: I found a "modify_date" column when executing "SELECT * FROM sys.tables ORDER BY modify_date desc", but this seems to only keep track of modifications to the table's structure, not its contents.

Answer 1

replace spt_values with the tablename you are interested in, the query will give the the last time it was used and what it was used by

From here: Finding Out How Many Times A Table Is Being Used In Ad Hoc Or Procedure Calls In SQL Server 2005 And 2008

SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
         THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement like '%spt_values%'  -- replace here
AND sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

Keep in mind that if you restart the box, this will be cleared out

Answer 2

In Oracle you can use the ASH (Active Session History) to find info about SQL that was used. You can also perform code coverage tests with the Hierarchical profiler, where you can find which parts of the stored procedures is used or not used.

If you wonder about the updates on table data, you can also use DBA_TAB_MODIFICATIONS. This shows how many inserts, updates, deletes are done on a table or table partition. As soon as new object statistics are generated, the row for the specified table is removed from DBA_TAB_MODIFICATIONS. You still have help here, since you could also have a peek in the table statistics history. This does not show anything about tables that are queried only. If you really need to know about this, you are to use the ASH.

Note, for both ASH and statistics history access, you do need the diagnostics or tuning pack license. (normally you would want this anyway).