SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Comparing Stored Procedures, Part 1

Comparing Stored Procedures, Part 1 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jesse McLain
Posted Monday, February 09, 2009 1:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 05, 2009 7:28 AM
Points: 230, Visits: 441
Comments posted to this topic are about the item Comparing Stored Procedures, Part 1

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #653190
mstjean
Posted Tuesday, March 10, 2009 11:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 05, 2009 1:45 PM
Points: 67, Visits: 496
You may want to check out Ultra Edit. Save 2 usp's to text files and do File/Compare Files. It shows the files side by side with the extra/missing rows highlighted.

It also does column mode editing which comes in handy.

Cursors are useful if you don't know SQL
Post #672628
Jesse McLain
Posted Tuesday, March 10, 2009 11:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 05, 2009 7:28 AM
Points: 230, Visits: 441
UltraEdit is a great product, I used to use it extensively in the past. I don't think it had the 'compare files' feature back then.

This effort to compare stored procedures has gone through several iterations, the most recent of which uses a recursive CTE: http://www.sqlservercentral.com/scripts/TSQL/66074/. You can also check out the blog entry here: http://jessesql.blogspot.com/2009/02/comparing-stored-procedures-part-6.html, which explains a lot of my thought process, and demonstrates some performance charting.

Using the most recent version above, you can run this query below on the results to achieve a visual comparison:

SELECT 
Seq1_Line = ISNULL(LTRIM(STR(S1.CodeLineNum)), '')
,ISNULL(S1.CodeLineTxt, '')
,ISNULL(S2.CodeLineTxt, '')
,Seq2_Line = ISNULL(LTRIM(STR(S2.CodeLineNum)), '')
,OrderBy = ISNULL(S1.CodeLineNum, S2.CodeLineNum)
FROM Seq1 S1
FULL OUTER JOIN Seq2 S2
ON S1.CodeLineNum = S2.MatchLineNum
ORDER BY OrderBy

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #672643
P Jones
Posted Wednesday, March 18, 2009 4:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 280, Visits: 404
I just script my stored procs off the two servers to text file and use WinDiff to compare side by side. No cost. No hassle.
Post #678240
tim-707944
Posted Tuesday, April 28, 2009 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 21, 2009 4:38 PM
Points: 2, Visits: 25
This solution works great as long as the database servers are both accessible. In our shop, our Dev, Test, and Production systems are walled off from each other, so there is no reliable way to do that sort of querying.

In our case, I typically just script them out and use BeyondCompare to find differances.
Post #706148
Pratap Prabhu
Posted Tuesday, May 26, 2009 10:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 10:49 AM
Points: 26, Visits: 70
I myself prefer using Visual Source Safe. I use this for my StoredProcs on my dev box. For mismatches between the DevBox and the Staging servers I use a stored proc I developed a few years ago ( sp_utl_FindMisMatchedObjects published here on SQLServerCentral http://www.sqlservercentral.com/scripts/Development/63270/ ) The stored proc can also be used against a Production server, but in my setup the production servers are not directly accessible from the Staging and Dev servers.


Post #723385
Larissa-136281
Posted Thursday, September 17, 2009 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 01, 2009 10:15 AM
Points: 12, Visits: 10
I have script that will compares 2 objects. It produce result similar to windiff. You don't need to export object
posted in scripts http://www.sqlservercentral.com/scripts/Maintenance/64277/
Post #790000
jswong05
Posted Thursday, October 01, 2009 9:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 27, Visits: 66
Thanks for the effort. Since SPs are just ASCII text, any diff program will do. diff.exe comes from windows free resource kit download. Any programmers should be able to diff.

http:/usa.redirectme.net
Post #796452
« Prev Topic | Next Topic »


Permissions Expand / Collapse

 
 
Copyright © 2002-2009 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use