When you work with SQL Server, you regularly want to know when an object is last executed, last modified, etc.. Well, at least I do! In most cases you need to use the sys.objects to obtain this information, join it with other sys tables, search for your information, and so on. But there is an easier way.

The script below will get you the latest information of any object in your database.

USE SandBox
SELECT
DB_NAME(Txt.dbid) AS DatabaseName,
OBJECT_NAME(Txt.objectid, Txt.dbid) AS ProcedureName,
Objects.create_date AS CreationDate,
Objects.modify_date AS ModifiedDate,
MAX(Stats.last_execution_time) AS Last_Execution,
CASE Objects.type WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'IT' THEN 'Internal table'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'SN' THEN 'Synonym'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'SQL DML Trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE '-'
END AS ObjectType
FROM sys.dm_exec_query_stats Stats
CROSS APPLY sys.dm_exec_sql_text(Stats.sql_handle) Txt
LEFT JOIN sys.objects Objects
ON Objects.object_id = Txt.objectid
WHERE 1 = 1
AND Txt.dbid = db_id() --Execute for selected DB
AND Txt.dbid IS NOT NULL --Exclude Ad-Hoc queries
AND DB_NAME(Txt.dbid) IS NOT NULL --Exclude Ad-Hoc queries
GROUP BY
DB_NAME(Txt.dbid),
OBJECT_NAME(Txt.objectid, Txt.dbid),
Objects.create_date,
Objects.modify_date,
Objects.type
ORDER BY DatabaseName, ProcedureName ASC

This script will show you the information you need to decide if you want to keep the object or, for example, want to delete it. With this script you can also check if Functions are still in use, or when a Trigger is last fired.

If you need more information about the object you can also use the query below. This will show you the time needed to execute the object, last execute time needed, etc: