T-SQL – Compare Stored Procedure Versions Between Two Databases

If you have several different versions of the same database then you will want to be able to check for differences in the objects between the databases. You can quickly and easily do so using SQL Server’s sys.objects or if you are specifically looking up stored procedures, SQL Server’s sys.procedures system views.

Let’s take an example use case where if you have a development and a deployment database and you want to check what procedures in development that you (or someone on your team) has worked on that you should now move to deployment. Normally it’s not too difficult to remember, but if there are a large number of small changes or a long time has passed since you made the changes, then you’ll need to run a simple difference comparison between the two databases to remember what procedures you need to migrate.

Luckily you can simply type in your comparison query in SQL Server Management Studio (SSMS). This is as powerful and granular as you want it to be and can be just as accurate as specially written custom tools. Unlike custom tools though, this method comes with SQL Server and just requires a bit of understanding of the sys.objects system view.

Have a look at other articles I’ve written about SQL Server if you find this article to be useful.

So Other than SSMS What do I Need to Compare my Stored Procedures?

It’s quite simple, to be able to  compare your procedures you’ll need an account that has permission to query sys.objects and you will need a way for your query to span both databases.

If your databases reside on different database servers then you will have to set up a linked server on your development database server that points to your deployment server. Once  you have this in place you can write some simple queries to compare the stored procedures in your development database with those on your deployment database.

Writing the Comparison of Stored Procedure Differences

Once you have a way to query both of your databases, then you are ready to run your comparison.

The simplest check for differences is by doing a left join of stored procedure names from your development database to names on your deployment database and then filtering the results by returning procedures where the modified date in development is later than the modified date in deployment. This will give you an encompassing view of everything that’s different and more recent in development than in deployment.

Here are two examples using first the sys.procedures and then the sys.objects system views. These expand on an earlier article on the subject that I wrote explaining how to use these views.

If you are undecided which version of the system views to choose, then I’d advise choosing whichever you feel most comfortable with since they end up returning the same information for your stored procedures. However if you wish to search more objects than just stored procedures, then use the sys.objects versions of the queries below since you can update them to include other database objects such as views, tables, or user defined functions.

Please note that in the examples below you’ll need to replace the table names and linked server name with the correct information from your database. For clarity I’ve highlighted the database name references in orange, and I’ve marked the linked server in green and have prefixed it with XSVR.

sys.objects Version

SELECT tblA.name,tblB.name,tblA.modify_date,tblB.modify_date
 FROM MyDBName.sys.procedures tblA
 LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.procedures tblB ON tblA.name=tblB.Name
 WHERE tblA.modify_date > ISNULL(tblB.modify_date,1900-01-01)
 ORDER BY tblA.modify_date DESC

 sys.procedures Version

SELECT tblA.name,tblB.name, tblA.modify_date,tblB.modify_date
 FROM MyDBName.sys.objects tblA
 LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.objects tblB ON tblA.name=tblB.Name
 WHERE tblA.type = 'P'
 AND tblA.modify_date > ISNULL(tblB.modify_date,1900-01-01)
 ORDER BY tblA.Modify_date desc

The report generated by these queries should really help you get a good overview of all the procedures in your development database that are more up to date than those on your deployment database.

Narrowing Down your Difference Queries Even Further

If you are interested in further narrowing down just the procedures that you have changed, then you will need some marker text in your procedures to go by, so I hope you have been adding your name to the comments section (or have been marking them in some other identifiable way).

If you have been marking your procedures, then you can narrow your search to only modified procedures containing your specific marker. You can do so by getting the stored procedure text from the sys.sql_modules view that you can join by object_id.

Here are our two examples from above that tie in the  sys.sql_modules view to narrow down the list of procedures by marker text within the procedures:

sys.objects Version

SELECT tblA.name,tblB.name, tblA.modify_date,tblB.modify_date
FROM MyDbName.sys.objects tblA
INNER JOIN MyDbName.sys.sql_modules tblModules ON tblA.object_id = tblModules.object_id
LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.objects tblB ON tblA.name=tblB.Name
WHERE tblA.type = 'P' 
AND tblA.modify_date > ISNULL(tblB.modify_date,1900-01-01)
AND tblModules.definition like '%Some Marker Text%'
ORDER BY tblA.Modify_date desc

sys.procedures Version

SELECT tblA.name,tblB.name,tblA.modify_date,tblB.modify_date
FROM MyDbName.sys.procedures tblA
INNER JOIN MyDbName.sys.sql_modules tblModules ON tblA.object_id = tblModules.object_id
LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.procedures tblB ON tblA.name=tblB.Name
WHERE tblModules.definition like '%Some Marker Text%'
AND tblA.modify_date > ISNULL(tblB.modify_date,1900-01-01)
ORDER BY tblA.modify_date DESC

Doing a Content Difference Check

Now let’s say we don’t want to rely entirely on the modification date to determine if our stored procedures in deployment are different. Instead we want to only see procedures where the actual code is different. We can even further narrow down our queries above by doing a direct text comparison of the procedures. This might add some processing time to your query depending on your database size, but the processing time is nothing in comparison to having to manually look through each procedure.

sys.objects Version

SELECT tblA.name,tblB.name, tblA.modify_date,tblB.modify_date
FROM MyDbName.sys.objects tblA
INNER JOIN MyDbName.sys.sql_modules tblModules ON tblA.object_id = tblModules.object_id
LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.objects tblB ON tblA.name=tblB.Name
LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.sql_modules tblModulesB ON tblB.object_id = tblModulesB.object_id
WHERE tblA.type = 'P' 
AND tblA.modify_date > ISNULL(tblB.modify_date,1900-01-01)
AND tblModules.definition like '%Some Marker Text%'
AND tblModules.definition <> ISNULL(tblModulesB.definition,'NA')
ORDER BY tblA.Modify_date desc

sys.procedures Version

SELECT tblA.name,tblB.name,tblA.modify_date,tblB.modify_date
FROM MyDbName.sys.procedures tblA
INNER JOIN MyDbName.sys.sql_modules tblModules ON tblA.object_id = tblModules.object_id
LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.procedures tblB ON tblA.name=tblB.Name
LEFT OUTER JOIN XSVRMyServerName.MyDbName.sys.sql_modules tblModulesB ON tblB.object_id = tblModulesB.object_id
WHERE tblModules.definition <> ISNULL(tblModulesB.definition,'NA')
AND tblA.modify_date > ISNULL(tblB.modify_date,1900-01-01)
AND tblModules.definition like '%Some Marker Text%'
ORDER BY tblA.modify_date DESC