Ygthkb

I use sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan and sys.dm_db_index_usage_stats.last_user_lookup to query last time a table was read.

For views, functions and stored procedures I'm using sys.objects but it doesn't have any field for read datetime.

Is there any system table where I can see the last time these types of objects were read/used?

My objective is to find objects that aren't being used and possibly drop them.

Unfortunately, there is no 100% accurate way to determine this. However, you can get as close as possible by examining all code that could potentially make DB calls, as well as the definitions of all of your DB objects and SQL Server Agent jobs. There are often code paths that are infrequently accessed and hence could easily not show up in DMVs, extended events, SQL Profiler traces, or even custom logging. Checking ALL code will get you as close as possible.

The only area left out of an exhaustive code search is ad hoc queries that are not in code anywhere. In this case DMVs, traces, etc at least have the potential to identify usage, but are certainly no guarantee.

Unless you have a server side trace running or XEvent running, there is no guaranteed way of telling a particular object is used or not. Some objects might be referenced by a report that is ran once a year by your CIO or business user. So unless you have a concrete data for your business life cycle, there is always a room for error.

I would suggest that once you identify possible candidates for removal, do not drop them, instead rename them and after a year or so, drop them.

When using DMVs you need to log them to a persisted table as DMV data get flushed out due to instance restart, certain sp_configure changes, etc.

I have answered a similar question here that will get you started.

Your best bet without built in logging would be using the below query:

select s.last_execution_time

from sys.dm_exec_query_stats s

cross apply sys.dm_exec_query_plan (s.plan_handle) p

where object_name(p.objectid, db_id('DBNAME')) = @proc_nm

There is a dedicated DMV for stored procedures.

select DB_NAME(eps.database_ID) AS DatabaseName

, OBJECT_NAME(eps.object_ID) AS ObjectName

, eps.type_desc

, eps.last_execution_time

, eps.execution_count 



from sys.dm_exec_procedure_stats eps



WHERE database_id = DB_ID()

The DMV will show the execution stats since the last server restart or detach (might need to confirm that). Meaning if a stored procedure has not been run since the database was attached or restarted, there will be no entry in this dmv. You can join it against the object table to find NULL's and assume what has not been run.

If you need trigger activity, you can use the same query as above except use sys.dm_exec_trigger_stats as the table.

For DMV to give correct results for all databases, we can modify the script shared by @dfundako as below:

 SELECT

    DB_NAME(eps.database_ID)                    AS DatabaseName

   ,OBJECT_NAME(eps.object_ID, eps.database_id) AS ObjectName

   ,eps.type_desc

   ,eps.last_execution_time

   ,eps.execution_count   

FROM sys.dm_exec_procedure_stats eps