Hello, I would like to find when a (several) user defined function was used. I have a process that looks at sys.dm_exec_procedure_stats to get when a stored procedure was used and I even use sys.dm_exec_trigger_stats for triggers.
The process does not seem to update the last worked for a udf (object type FN or IF) which I am looking in the procedure stats view.
So my question is, is there a view or something where I can look at to get the last_execution_time for a udf?
WITH ForSource AS (
SELECT
'mydbname' AS DatabaseName,
O.name AS ModuleName,
CASE O.Type WHEN 'P' THEN 'USP' WHEN 'FN' THEN 'UDF' WHEN 'IF' THEN 'UDF' WHEN 'TR' THEN 'TRIG' END AS ModuleType ,
O.create_date,
O.modify_date,
MAX(D.last_execution_time) AS last_execution_time
FROM [mydbname].sys.objects O
LEFT JOIN sys.dm_exec_procedure_stats D ON 'mydbname' = DB_NAME(d.database_id)
AND O.name = OBJECT_NAME(D.object_id, database_id)
WHERE O.is_ms_shipped = 0
AND O.[type] in ('P','FN','IF','TR')
AND o.name NOT LIKE 'dt%'
AND o.name NOT LIKE 'SP%'
AND o.name NOT LIKE 'fn%'
GROUP BY O.name, O.type, O.create_date, O.modify_date
)
MERGE myholdingDB.dbo.ListStoredProceduresUse AS target
USING ForSource AS source ON (target.DatabaseName = source.DatabaseName AND target.ModuleName = source.ModuleName)
WHEN MATCHED THEN
UPDATE SET LastUsedDate = CASE WHEN source.last_execution_time IS NULL THEN target.LastUsedDate ELSE source.last_execution_time END
WHEN NOT MATCHED THEN
INSERT (DatabaseName, ModuleName, ModuleType, CreateDate, ModifyDate, LastUsedDate)
VALUES (DatabaseName, ModuleName, ModuleType, create_date, modify_date, last_execution_time); Thank you,
djj