Extended properties of database objects

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:

SELECT * FROM sys.dm_exec_query_stats Stats
CROSS APPLY sys.dm_exec_sql_text(Stats.sql_handle) Txt 

 
With these two queries you will be able to determine the status of the objects in your database.