SQLTeam.com | Weblogs | Forums

When was a user defined function last used

sql2008r2

#1

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


#2

No, SQL does not retain that info.


#3

Thanks Scott, I was afraid of that. Oh, well....