July 27, 2007 at 1:54 pm
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
Redgates SQL compare is good, you have to pay for it but it makes this really simple.
July 27, 2007 at 2:17 pm
July 27, 2007 at 3:01 pm
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
Another vote for SQL Compare - very fast and accurate - well worth the cover charge!
Regards,
Harley
July 30, 2007 at 5:24 am
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
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
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
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