Determine when a Stored Procedure was created and last altered

  • Comments posted to this topic are about the item Determine when a Stored Procedure was created and last altered

  • 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

  • [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]

  • 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

  • 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. 😎

  • Thanks for this.

    I had been looking for something that did not require access to the current trace file to show this data.

  • Nice little script. Thank you.

  • 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