Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing Stored Procedures, Part 1 Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 1:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 4, 2014 11:21 AM
Points: 305, Visits: 566
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
Posted Tuesday, March 10, 2009 11:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:34 AM
Points: 436, Visits: 2,285
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
Posted Tuesday, March 10, 2009 11:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 4, 2014 11:21 AM
Points: 305, Visits: 566
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
Posted Wednesday, March 18, 2009 4:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:02 AM
Points: 563, Visits: 1,175
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
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, June 4, 2012 9:50 PM
Points: 4, Visits: 29
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
Posted Tuesday, May 26, 2009 10:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148
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
Posted Thursday, September 17, 2009 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 7:24 AM
Points: 12, Visits: 25
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
Posted Thursday, October 1, 2009 9:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 13, 2014 12:30 AM
Points: 27, Visits: 470
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.

Jason
http://dbace.us
Post #796452
Posted Friday, December 11, 2009 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148
Hi

Back in 2008 I posted a similar script 'Find Mismatched Views and Stored Procs' (http://www.sqlservercentral.com/scripts/Development/63270/), given 2 databases it lists out the mismatched Views and stored Procs. The Extreme right columns are the stored procs/views scripts. Check it out, maybe it could be some help. (Note:If the databases are on different physical servers just create a linked database)





Post #833068
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse