Syncing stored procedures between two databases?

Question

For an app my team is developing, I have created a copy of the database to work independently on some possible features for the system. In doing so, I have changed some stored procedures. Meanwhile, other members of the team continue to work on the other database and change some of those stored procedures.

The code is still calling the same SP names, so I'll need to know which stored procedures have been changed in order to make the correct fixes. Is there any way to take all of the stored procedures from one database and match it against SPs with the same name in the other database, to see which are different (besides the USES[DB1] and USES [DB2] difference).

Thanks

Answer 1

Ouch, this is something you should have considered earlier. One approach: extract the SP code to files and use a tool like Windiff.

Or if you have to do it in the database, check out these tools.

In the future, put your SP scripts into source control. You could have branched and merged the changes.

Answer 2

I would recommend purchasing (or just evaluating) a dedicated tool to achieve this. There are a number of products on the market including: Apex SQL Diff and Redgate SQL Compare

I have used Apex SQL Diff for a number of years and would wholeheartedly recommend it.

Answer 3

One of the features available inside of Visual Studio is called Schema Compare which will compare all database objects such as tables, views, sprocs, etc. Not sure if you have that tool available to you or not.

Another feature allows you to compare the data in the databases which has been a savior already.

Answer 4

to find when things changed use this:

SELECT
    CONVERT(varchar(23),modify_date,121) AS modify_date
        ,type_desc
        ,name
    FROM sys.objects
    WHERE is_ms_shipped=0
    ORDER BY 1 DESC

it will list the following types of items:

CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
PRIMARY_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
UNIQUE_CONSTRAINT
USER_TABLE
VIEW

Also, you might want to look into something like CVS or sourcesafe, so you can check out/in code and have merge/branch capabilities.

script everything to separate files and use a tool like Beyond Compare (has a free trial) http://www.scootersoftware.com/moreinfo.php It can compare directories and allow you to view diffs per file too, with merge capabilities, etc.

Answer 5

Hey all. I took all of the responses into consideration and downloading new software is not an option for me. Upon further research, this is how I went about this solution.

I simply queried both databases for a list of all stored procedures and their most recent alter date. I then compared their alter date and manually looked at their source and made my changes. Source control definitely would have helped with this, but sometimes things are out of my control.

The code to get a list of all of my SP names and alter dates is as follows.

select ROUTINE_NAME, LAST_ALTERED
from information_schema.routines
WHERE
Routine_Type='procedure'
AND specific_Catalog='DATABASE_NAME' ORDER BY LAST_ALTERED