Last Updated SP's Details?

  • Hi All,

    We are having around 60 sp's, All the developers will work on those sp's.

    In that i want to know the recent updated sp's because of some reasons...

    Is there any ways to find out those recently updates sp's.

    Pls help me..:-)

    Regards
    Chowdary...

  • What "sp's" are you talking about?

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Hi Shawn thank you for your response,

    In my db we are having around 60 Stored procedures(User defined ) ,in that yesterday some of the developers has updated some SP's , Now i want to track those updated Stored procedures..

    Regards
    Chowdary...

  • What you are referring to I believe would be done through source control. Unless you are already tracking object level changes in your database there is no information natively captured by SQL Server that is going to tell you.

    Only thing I know you could do would be going through your recent backup of the database prior to the changes being deployed. I mean restore your database to another location, not overwriting your current one, and pull all the objects for comparison.

    How you do the comparison itself can be done through Powershell, manually, or third party products.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Thank you Mr.Shawn,

    I ll do some RnD and let you know...

    Regards
    Chowdary...

  • Hi,

    with the following query you can know which is the latest updated SP

    select * from sys.procedures order by modify_date desc

  • Use below query on your DB

    select name,modify_date from sys.objects where type='P' order by modify_date desc

  • Thank you Mr.Sasidhar n Mr.Mahesh..

    Thanks a lot....

    Regards
    Chowdary...

  • You should include created date if you are going to try and pull it from those queries above. Unless you specify that your developers are altering procedures, it is a more common practice to drop and create than to alter in some shops.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Hi Shawn..

    If we use this

    "select name,modify_date from sys.objects where type='P/U/PK/FK/S/IT' order by modify_date desc"

    we will get all the details like creation date,Update date...

    Regards
    Chowdary...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply