Post reply

Last Updated SP's Details?

  • November 16, 2014 at 9:32 pm

    #301129

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

  • November 16, 2014 at 9:39 pm

    #1758862

    What "sp's" are you talking about?

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

  • November 16, 2014 at 9:51 pm

    #1758863

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

  • November 16, 2014 at 10:00 pm

    #1758864

    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

  • November 16, 2014 at 10:15 pm

    #1758865

    Thank you Mr.Shawn,

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

    Regards
    Chowdary...

  • November 16, 2014 at 11:42 pm

    #1758873

    Hi,

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

    select * from sys.procedures order by modify_date desc

  • November 16, 2014 at 11:53 pm

    #1758874

    Use below query on your DB

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

  • November 17, 2014 at 3:08 am

    #1758903

    Thank you Mr.Sasidhar n Mr.Mahesh..

    Thanks a lot....

    Regards
    Chowdary...

  • November 17, 2014 at 4:15 am

    #1758925

    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

  • November 17, 2014 at 9:40 pm

    #1759150

    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