Wednesday, May 21, 2008

SQL Server Stored Procedure Compare.

Simple one that can save a lot of trouble.

Heres the scenario:

Your application is making heavy use of stored procedures, and your database is hosted on multiple SQL servers say live & development at the simplest. Your latest batch of changes are ready and you're compiled a list of the Stored procedure updates you require along the way... but you're just not entirely sure you've captured all the changes.

The solution:
Export your SQL procedures from both servers and compare them. To do so make sure the Summary panel is showing ( usually is when you sign on)

click through to the database you are interested in, then open Programmability\Stored Procedures. you should see a list of your stored procedures
(if not check you have view permissions for them) . You can then select them all. Right click and choose "SCRIPT stored procedure as..">"CREATE to"> "> "file" from the context menu. Then simply choose an appropriate file.

Once you've done the above for both databases you can simply file compare using one of the many excellent tools out there( I use Beyond Compare myself).

And while you have them in this format you might as well put them into source control. Maybe even make it part of your build script to install them. but thats another days work

This is based on SQL Server 2007, using the SQL Server Management studio .


zoom777 said...

Disculpa, creo q te equivocaste: SQL Server 2007????

Anonymous said...

This is a easy and straightforward way to do this. I didn't reallize you could use the Object Explorer Details to select multiple items. Most DB schema comparison tools I've used are a little unwieldy and slow.

From SSMS I script to clipboard instead. I use Notepad++ for file comparisons: Double click to create a new tab, paste, doubleclick to create another tab and paste the script from the other DB. Then use the Compare plugin in Notepad++.