Room of a pleinolijf

Ask yourself this: how do I want to be remembered ?

How To See Last Modified Date of Objects in SQL Server 2005

6 Comments

A handy tip for those of you not yet all too familiar with SQL Server 2005 and getting lost looking for an option to show them the last modified date of -for example- a stored procedure. There is none ! Just execute below T-SQL in the context of your database.

SELECT
[name],
create_date,
modify_date
FROM sys.procedures
WHERE
[type] = 'P'
AND is_ms_shipped = 0
AND [name] NOT LIKE 'sp[_]%diagram%'
ORDER BY
modify_date DESC

Obviously, you can do the same for tables, using sys.tables:

SELECT
[name],
create_date,
modify_date
FROM sys.tables
ORDER BY
modify_date DESC

With thanks to Binary Elves.

Advertisements

Author: pleinolijf

father | volleyballer | software engineer

6 thoughts on “How To See Last Modified Date of Objects in SQL Server 2005

  1. Nice and simple

    Like

  2. Great tips, many thanks.

    Like

  3. How to update the modifydate for truncate tables.
    for e.g. i have jobs running everyday and some of the tables are simply truncated and created, in this scenerio the modify_date (sys.all_objects) doesnt change .
    Please help me with function where the modify_date is the job run date for truncate tables

    Many Thanks
    Prav

    Like

  4. thank you for the simplicity

    Like

  5. Thanks buddy, you save my lot of time.

    Like

  6. Pingback: MSSQL: Check modified date of objects | blog.secu3r.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s