Looking for way to determine last update or change date on a stored procedure

In query analyzer is their anyway to determine when the time/date that a stored procedure was last modified? It would be nice if there were some type of log that would list all of the stored procedure in a particular database, and their last update date.
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
How's this float your boat...
SELECT name, create_date, modify_date 
FROM sys.procedures
ORDER BY name

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MlandaTCommented:
SELECT name, create_date, modify_date 
FROM sys.objects
WHERE [type] = 'P' 
	AND [name] = 'stored_proc_name'

Open in new window

PortletPaulEE Topic AdvisorCommented:
don't forget, to update an object such as a stored procedure, typically you delete it, then (re)create it

so I don't see much point in the "modify_date" in most cases.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MlandaTCommented:
Does the ALTER PROCEDURE not just modify an existing procedure? It certainly doesn't delete it and recreate it as a new object. In that case, then modified date works.

Imagine the chaos in replicated environments if one couldn't just modify the existing object. You'd have to keep redoing the snapshots for merely fixing a big in a stored proc. Renaming objects is a different story though. There I think you drop  and recreate, behind the scenes.
PortletPaulEE Topic AdvisorCommented:
IF folks use ALTER then that's fine

however some don't preferring to delete then recreate (i.e. there is nothing to stop either approach being used)

I am just being cautious about the relevance of the dates, that is all.
MlandaTCommented:
You had claimed that in MOST CASES the modify date will be irrelevant because people TYPICALLY  drop and recreate. I think it's safe to assume that the question's author is keenly aware of their environment and how they work, and for them, they are interested in modify_date.

I'll go out on a limb and say that in MOST replicated environments people ALTER as opposed to dropping then recreating (not that the author suggests replication, but it's an interesting focus group).
PortletPaulEE Topic AdvisorCommented:
sorry, it depends on the conventions adopted by individuals/organizations, I definitely overstated it

I repeat though, I m only being cautious
brgdotnetcontractorAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.