Geeks With Blogs


DevJef's Mumbo-Jumbo «There's a bit of SQL in all of us»

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

    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
    OBJECT_NAME(Txt.objectid, Txt.dbid),
    Objects.create_date,     Objects.modify_date,
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:


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.

Posted on Wednesday, October 19, 2011 8:59 AM SQL Scripts , SQL Server | Back to top

Comments on this post: Extended properties of database objects

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © DevJef | Powered by: