how to find last 10 date times when SP was Modified

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!

Answer 1

Create your own trace.

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

Answer 2

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.

Answer 3

You could implement a DDL Audit using DDL Triggers

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Thanks everyone

Answer 5

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

or 

Spname      nooftimespexecutedSincerestart

abc          250              

 

something  that? please let me know ...

Answer 6

See TechNet Script Center: Stored Procedure Execution Statistics

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

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

Answer 8

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