how to find last 10 date times when SP was Modified RRS feed

  • Question

  • hi all, 

    i am able to find out last modified date of SP, but is there a way to find out last modified sp history?

    Please let me know!

    Friday, March 18, 2016 1:07 PM


All replies

  • Create your own trace.

    The default trace may cover it, but it is not persistent when restarting the server.

    Friday, March 18, 2016 1:16 PM
  • You can't.  There is nothing built into SQL Server to track changes to an object.  If you didn't create something ahead of time, that data is lost.

    Friday, March 18, 2016 1:21 PM
  • You could implement a DDL Audit using DDL Triggers

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, March 18, 2016 1:42 PM
  • Thanks everyone
    Friday, March 18, 2016 7:00 PM
  • is there a way to find how many times the sp got called/executed since last restart?

    Spname      nooftimespexecuted   executeddatetime    

    abc           1                                  nov10th 11pm 2015

    abc           1                                 nov10th 11:05pm 2015

    abc           1                                 nov10th 11:05pm 2015


    Spname      nooftimespexecutedSincerestart

    abc          250              


    something  that? please let me know ...

    Friday, March 18, 2016 7:04 PM
  • See TechNet Script Center: Stored Procedure Execution Statistics

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Eric__Zhang Monday, March 21, 2016 4:21 AM
    • Marked as answer by Eric__Zhang Wednesday, April 6, 2016 8:13 AM
    Friday, March 18, 2016 7:13 PM
  • This sounds like the job of an audit tool and not SQL. What are you using for your audit tool? Why does the text of your stored procedures not have the modification dates and other metadata in the header as a comment?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 18, 2016 8:29 PM
  • Hi coolguy123SQL,

    I am not sure it would be useful for you but in SQLk12 you have the possibility to create AUDITING stuff under SSMS->Security

    Saturday, March 19, 2016 1:53 PM