January 14, 2014 at 10:07 pm
Comments posted to this topic are about the item Determine when a Stored Procedure was created and last altered
January 15, 2014 at 7:30 am
You can also use
select o.name as ProcedureName
, db_name(parent_object_id) as DatabaseName
--, sm.definition
, o.create_date as CreateDate
, o.modify_date as LastAlteredDate
from sys.sql_modules sm
inner join sys.objects o on sm.object_id = o.object_id
Benefit is the definition here is nvarchar(max) whereas in information_schema.routines it is nvarchar(4000)
Cheers
January 15, 2014 at 7:47 am
[font="Comic Sans MS"]Actually I would prefer to know the LAST TIME the procedure was run and by whom, in order to determine which ones have become unused fossils.
Any plans to upgrade your script to provide this ?[/font]
January 21, 2014 at 4:30 am
THis is also useful. Shows not only sprocs
SELECT DISTINCT
name
,type
,type_desc
,create_date
,modify_date
FROM
sys.objects
WHERE
type IN ('P','PK','F','D','FN','TR','UQ','X')
ORDER BY
type
,modify_date DESC
February 25, 2015 at 7:06 am
j-1064772 (1/15/2014)
[font="Comic Sans MS"]Actually I would prefer to know the LAST TIME the procedure was run and by whom, in order to determine which ones have become unused fossils.Any plans to upgrade your script to provide this ?[/font]
Wow... Bossy and un-informed.
That kind of information is not provided in the SQL server schema data which is what this script is about.
You would need to create a different script to monitor execution in the active trace real time.
Better idea for you and your production system would be to check out SQL Server Performance Monitor and run a trace. 😎
February 25, 2015 at 7:17 am
Thanks for this.
I had been looking for something that did not require access to the current trace file to show this data.
May 5, 2015 at 2:56 pm
Nice little script. Thank you.
April 27, 2016 at 1:45 pm
Now that I've used it I like it even better. Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply