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.
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
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