List all objects and related activities SQL Server 2008R2

Question

Is there a way to list all objects from a server (for all db) and its activities?

What I mean by activities:

• If an object is a table/view, I'd like to know if last time something got updated or this table was accessed.
• If an object is a function, I'd like to know last time function used.
• If an object is a stored procedure, I'd like to know last time executed.

Goal is to eliminate some of the non-used objects or at least identify them so we can further analyze it. If there is a better way to do this please let me know.

Answer 1

Without a specific audit or explicit logging instructions in your code what you are asking might be difficult to achieve.

Here are some hints that, in my opinion, can help you retrieving the information you need:

1. Tables/Views You can rely on dynamic management view that record index information: sys.dm_db_index_usage_stats (more info here)

   SELECT last_user_update, *
   FROM sys.dm_db_index_usage_stats
   WHERE database_id = DB_ID('YourDBName')
   AND OBJECT_ID = OBJECT_ID('[YourDBName].[dbo].[YourTableName]') 
   

2. Stored Procedures If SP execution is still cached you can query sys.dm_exec_procedure_stats (more info here)

   select last_execution_time, * 
   from sys.dm_exec_procedure_stats
   WHERE database_id = DB_ID('YourDBName')
   AND OBJECT_ID = OBJECT_ID('[YourDBName].[dbo].[YourSpName]')
   

3. Functions If function execution is still cached you can query sys.dm_exec_query_stats (from this great answer), more info here

   SELECT qs.last_execution_time
   FROM sys.dm_exec_query_stats qs
      CROSS APPLY (SELECT 1 AS X
       FROM sys.dm_exec_plan_attributes(qs.plan_handle)
       WHERE  ( attribute = 'objectid' 
                AND value = OBJECT_ID('[YourDBName].[dbo].[YourFunctionName]') )
               OR ( attribute = 'dbid' 
                    AND value = DB_ID('YourDBName') )
       HAVING COUNT(*) = 2) CA