Post reply

Comparing Two stored Procedures

  • July 27, 2007 at 1:54 pm

    #75167

    I have been given the dubious task of evaluating the stored procedures from two environments (DEV & TEST).

    There are over 100 stored procedures on each...so I am curious if anyone has any tried and true methods to comparing inequalities between two stored procedures?

    I had planned on scripting out only the stored procedures on each server into a ANSI file and getting an application to compare the files...however I can't seem to find one that is user friendly.

    Thanks in advance,

    Leeland

  • July 27, 2007 at 2:13 pm

    #723059

    Redgates SQL compare is good, you have to pay for it but it makes this really simple.

  • July 27, 2007 at 2:17 pm

    #723061

    i have used redgates sql compare, and its very good. you can download a trial copy which expires in 15 days, do your tests and convince your bosses to buy it

    Everything you can imagine is real.

  • July 27, 2007 at 3:01 pm

    #723074

    Here is an old post

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=113390

    I use the free yessoft program and it works great.

  • July 27, 2007 at 5:35 pm

    #723100

    Another vote for SQL Compare - very fast and accurate - well worth the cover charge! 

    Regards,

    Harley

  • July 30, 2007 at 5:24 am

    #723255

    If you use VSS at your company, it works fine for this as well.  Script all of the procedures into two different folders and use VSS to compare the folders ad files.

  • July 30, 2007 at 6:17 am

    #723265

    FWIW, the text of stored procedures is available in the sys.objects view as ObjectDefinition.

    The below is a procedure I run daily to capture any changes in my stored procedures/functions/etc. Perhaps you can get a hint from it to give you start. (You will need to restrict the sys.objects.type in the where clause, remove references to my local table CodeArchive and the append, etc.)

    ALTER PROCEDURE [dbo].[CodeArchiveAppend]

    AS

    BEGIN

     -- SET NOCOUNT ON added to prevent extra result sets from

     -- interfering with SELECT statements.

     SET NOCOUNT ON;

    INSERT INTO CodeArchive

    ( ObjectName

    , ObjectDescription

    , ObjectType

    , ObjectDefinition

    , ObjectID

    , CreationDate

    , ModifiedDate

    )

    SELECT so.name

    , so.type_desc

    , so.type

    , OBJECT_DEFINITION(object_id) as Source

    , so.object_id

    , so.create_date

    , so.modify_date 

     

    FROM      sys.objects so

    left join CodeArchive ca

    on so.object_id = ca.objectId

    and so.create_date = ca.CreationDate

    and so.modify_date = ca.ModifiedDate

    WHERE      so.[type] IN('C', 'D', 'P', 'FN', 'R', 'RF', 'TR', 'IF', 'TF', 'V')

    and ca.objectID is null --Insert record with new Create/Modified Date

    END

  • July 30, 2007 at 7:04 am

    #723287

    This is basically what I did...I found a free program as the company currently doesn't have anything purchased for this sort of task...and asking is like getting a diamond out of lump of coal...

    I used a program called ExamDiff

    Worked good...

    Thanks for the replies guys...

  • July 30, 2007 at 7:14 am

    #723292

    Vote for RedGate (it is a great product)  Another advantage is you can ignore things that you feel are not important to worry about. (such as updated comments).  Don't mistake this for thinking comments aren't important. 

    But I am curious if this post if for real.  I mean it's not like you could have missed the ad's for RedGate on this forum.

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

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