Solved

SQL Server Stored Procedure code compiled

Posted on 2014-03-25
3
504 Views
Last Modified: 2014-03-26
I am using SQL Server 2005 on a system using many stored procedures.
A standby server has the same stored procedures by due to on-going maintenance it may be possible that changes in the main server may not have been updated in every case.
Is it possible to compare the compiled stored procedure compiled code in the main server with the same stored procedure compiled code in the standby server ?
There may be differences in the source code due to varying comments so a textual comparison may yield false differences in functionality.
0
Comment
Question by:alcindor
  • 2
3 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 39954599
There are a few tools that may do what you need.

The most commonly used tool for this is a paid one - Red Gate's SQL Compare.  It is pricey, but good.  You may be able to kludge up a free version using two tools from Red Gate's free tools - Red Gate Scream (small tool to compare two Compare snapshots), and Red Gate Snapper (small tool to take a snapshot of a DB).

OpenDBDiff (at http://opendbiff.codeplex.com/) looks interesting and seems to do what you are asking for as well.  It is open source and free.

Also the Toad for SQL Server Freeware edition should be able to do this.  You can find it at http://www.toadworld.com/m/freeware/default.aspx

Hopefully, one or more of these will do what you need.
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 39954602
As far as I know, a comparison of compiled code (1) is not currently possible, and (2) could also result in inaccurate comparisons, because of different execution plans when compiled (which does happen, more often than you would think).  If one of the tools above allows you to exclude comments in the comparison, that should do the trick.
0
 
LVL 2

Author Closing Comment

by:alcindor
ID: 39955775
Thanks for your comments, I will check out the links supplied.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Restore Script - Syntax Error 8 106
tempdb log keep growing 7 43
Are triggers slow? 7 22
Substring works but need to tweak it 14 31
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question