Verify SQL Server Stored Procedures are the same on multiple servers

By:   |   Comments (17)   |   Related: > Stored Procedures


I have a stored procedure I push down to all of my servers that does a custom backup job and I want to make sure all servers have the same stored procedure. Is there some way to check without going to each server and reviewing the stored procedure?  Check out this to find out.


SQL Server doesn't offer any way for you to control this from within the standard set of tools. What I've done is created a procedure to compare one server that we know has the correct stored procedure against one or more other servers we want to check. With this simple procedure and a little SQL Server knowledge we're able to standardize all our servers and sit back and enjoy a job well done.

Manually Comparing SQL Server Stored Procedures

Recall that stored procedures are stored in the database they were created unless the DBA or developer specifies otherwise. Therefore we will need to connect to each database in question if we are going to compare stored procedures. In my case, we placed the stored procedure in the Master database because it was a backup script independent of user databases. To find the definition of those stored procedures we have a number of options. We could use SQL Server Management Studio (SSMS), right click the stored procedure and let SSMS script it out for us, but then we're still stuck trying to verify it's correct.

Scripting Stored Procedures in SQL Server Management Studio
Figure 1.

Comparing Code in sys.objects vs. the OBJECT_DEFINITION and HASHBYTES functions

Another option is to pull the object definition from sys.objects into a variable and compare it with the correct version. This probably isn't a bad option and when I first started working on this problem that was the method I started with. I wasn't sure if this accounted for things like white space, tabs, etc. so I looked for another option. I decided on using the OBJECT_DEFINITION function provided by Microsoft to assure I was getting the best results. If you're not familiar with the OBJECT_DEFINITION function it provides the definition of an object in a standard format. If I had to guess it's probably pulling the definition from sys.objects, but I found no evidence of that in the documentation. Review OBJECT_DEFINITION here.

Here are the steps we're going to follow to get the information we need:

  1. Get the object definition from the standardized server, i.e. the server which we know has the correct version.
  2. Get the object definition from the server(s) we are checking.
  3. Place both into a variable and compare the two.
  4. Notify us of the results so we can take action as necessary.
The following SQL script pulls the definition of our stored procedure from the standardized server:


A thing to note here is that SQL Server gives us the definition of the stored procedure (assuming it's not encrypted), but we also see that the output can be quite verbose. Although I could create a variable such as nvarchar (4000) to hold the data I'm not going to use this method; I'm going to take an additional step to use the HASHBYTES function to get a hash of the object definition which is easier for us to work with (more on that later). Follow this link to the Microsoft documentation on the HASHBYTES function. Also note that the HASHBYTES function has limits. It's not going to take all inputs of an object definition if they're greater than 8000 bytes.  In my testing I didn't find any stored procedures it couldn't work with, but I think it's worth noting that extremely large stored procedure definitions might cause you to revisit how to complete this task.

Implementing the HASHBYTES function is easy; the script below adds in a couple extra lines to get the hashed results as well as introducing some variables to make it easier to work with.

/***Some worker variables*********/
declare @spname nvarchar(4000) 
declare @spdefinition nvarchar(4000) 
declare @hashedVal varbinary(4000) 
/**Name of our stored procedure************/
set @spname = 'sp_SprocName' 
/**Get the object definition *************/
set @spdefinition = (SELECT OBJECT_DEFINITION (OBJECT_ID(@spname )))     
set @hashedVal = (select HashBytes('SHA1', @spdefinition)) 
/**Here's the hashed value of the stored procedure**********/
select @hashedVal 

After running this you'll receive a nice compact hashed version of your stored procedure you can more easily work with when comparing servers. I found this to be a much better option than copying what I found in sys.objects where stored procedure definitions could be hundreds of lines long.

HASHBYTES function value for a stored procedure
Figure 2.

Capture the Hashed Value for Stored Procedures on Multiple Servers

By using this script we now have a hashed value of the correct stored procedure definition. To get the hashed value of other servers to compare with the original we're going to make use of the SQL Server management console's ability to query multiple remote servers. Tim Ford's tip on querying multiple servers is a good place to review this technology and it's the method we'll use here.

Below we tweak our original script by adding the hashed value we found on the original server and we add in some nice outputs to help understand the status of our servers.

declare @correctHashedVal varbinary(4000)
/***We got the correct hashed value from above******/
set @correctHashedVal = 0x984E1923ACAE9E93C254E0EE72B244DDC5F056A7
declare @spname nvarchar(4000)
declare @spdefinition nvarchar(4000)
declare @hashedVal varbinary(4000)
    set @spname = 'sp_SprocName'
    set @spdefinition = (SELECT OBJECT_DEFINITION (OBJECT_ID(@spname )))    
    set @hashedVal = (select HashBytes('SHA1', @spdefinition))
if @hashedVal = @correctHashedVal
        select 'This server is ok'
    select '****Please review this server******'

After we run the script we're now sure of the status of each server and more importantly we're clear that each stored procedure is identical or a server needs to be reviewed.

HASHBYTES function value for multiple stored procedures
Figure 3.

Wrapping it up

There are some points mentioned above, but worth noting here too.

  1. The HashBytes function will not accept enormous sizes; review the Microsoft link for more information but be prepared to do some tweaking.
  2. The multi-server querying ability allows you to switch to a different database, but you'll need to call that in your script too with a "Use myDB" statement.
  3. There is an extremely tiny possibility that two stored procedures will be different, but still result in an "ok" message. The Microsoft article discusses collisions albeit extremely rare.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Burt King Burt King is Senior Database Administrator at enservio with more than 15 years experience with SQL Server and has contributed to since 2011.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 3, 2024 - 2:08:02 AM - vivek singh Back To Top (92140)
-------this script to check for all database to find hashed value----

DECLARE @spname NVARCHAR(4000)
DECLARE @spdefinition NVARCHAR(4000)
DECLARE @hashedVal VARBINARY(4000)


CREATE TABLE #HashedValues (
DatabaseName NVARCHAR(128),
StoredProcedureName NVARCHAR(4000),
HashedValue VARBINARY(4000)

FROM sys.databases
WHERE state_desc = 'ONLINE' -- Consider only online databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

PRINT 'Database: ' + @dbname

SET @sql = '
USE [' + @dbname + '];
FROM sys.procedures;
OPEN sp_cursor;
FETCH NEXT FROM sp_cursor INTO @spname;
SET @spdefinition = (SELECT OBJECT_DEFINITION(OBJECT_ID(@spname)));
SET @hashedVal = (SELECT HashBytes(''SHA1'', @spdefinition));
INSERT INTO #HashedValues (DatabaseName, StoredProcedureName, HashedValue)
VALUES (@dbname, @spname, @hashedVal);
FETCH NEXT FROM sp_cursor INTO @spname;
CLOSE sp_cursor;
DEALLOCATE sp_cursor;'

EXEC sp_executesql @sql, N'@spname NVARCHAR(4000), @spdefinition NVARCHAR(4000), @hashedVal VARBINARY(4000), @dbname NVARCHAR(128)', @spname, @spdefinition, @hashedVal, @dbname;

FETCH NEXT FROM db_cursor INTO @dbname

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #HashedValues

DROP TABLE #HashedValues

Saturday, July 17, 2021 - 12:52:14 PM - Sabir Back To Top (89025)

first thanks for this wonderful comparison script, but I do have a query here if we have same stored procedure in two different databases and both having same DML query that is insert into table 1, the only difference in second stored procedure is the extra break line , in that case also your script will not work, it says the hash values are diff and it gives the messages to review the server, so it should also eliminate or exclude the white space and only consider the actual implementation of stored procedure that is DML queries.

Saturday, October 15, 2016 - 4:40:17 AM - Gerald Back To Top (43569)


great. I had the same problem but I just added an extended property to the procedure called "Version" with the value of the script version.

For rollouts and verification I just query the extended property. And on deployments with an "alter procedure" I just do an update on it. 



Thursday, January 28, 2016 - 3:43:06 PM - John Hardin Back To Top (40533)

This hash function doesn't have the size limit:



Tuesday, November 19, 2013 - 8:47:02 AM - Anonymous User Back To Top (27540)

Thanks, this was really useful to me today

Tuesday, June 19, 2012 - 11:28:24 PM - Don Back To Top (18113)

Does anyone has an updated script for SQL 2000 ?

Monday, April 16, 2012 - 8:53:42 PM - Mohamed Back To Top (16955)
Free tools to compare the schéma, no need to write Any code Best regards

Wednesday, April 11, 2012 - 2:40:31 AM - Johan Back To Top (16844)

I do not think your solution is of use for most people.

It is simply easier to do a simple schema compare. Have you tried it?

Tuesday, April 10, 2012 - 7:58:20 AM - Burt Back To Top (16833)

Definately not the fastest method -- a number of options exist, certainly.  I chose this because it was free (my time) and I can't push down stored procedures without following a rigid change control process.  We had bantered about the idea of pushing down the source for each of our customer sprocs weekly but haven't moved in that direction.  

Monday, April 9, 2012 - 5:14:12 PM - Jorge Serres Back To Top (16827)

The solution is elegant but no so effctive since it'll take time. Why not use POSH OR SQLps (POWERSHELL) and redeploy the latest version in question to every server. I once needed to deploy in 285 servers a sproc that would handle (users with no roles/ orphaned users and logins with no child users).

The best approach was using POSH. It did not even take 45 secs. The sproc then needed to be updated with a small ran the .ps1 with new version and it happened just in secs.

Similarly, let's say we needed to re-configure the MAIL (db mail) for using a different account and a few other changes and we had over 2500 instances...The best approach is using POSH.

Monday, April 9, 2012 - 11:08:26 AM - Dave Winters Back To Top (16824)


Here is a solution that "finger prints" the USP using a password.


You can then verify the procedure has not been modified or is the same.



Wednesday, March 21, 2012 - 9:01:09 AM - Natali Back To Top (16560)

yes, we can use extended properties with more coding. They easier to be reached and programmed in .net/power shell code using MS Sql SMO classes. We can use Red Gate tools with no coding or their SDK library plus SMO with coding to build very detailed comparison with the synchronization..:) This article shows a good handy way to create a fast light comparison stored procedures. I use that way for fast reporting, for example.

Wednesday, March 21, 2012 - 5:07:37 AM - Wilfred van Dijk Back To Top (16554)

I use an extended property, which contains a version number. Easy to compare, but requires some strict programming rules

Tuesday, March 20, 2012 - 11:04:58 PM - Natali Back To Top (16550)

Thank you very much for hashbytes idea! Instead of using checksums, i  implemented HashBytes in my stored procedure that lists sql servers and compares a programmable object:

declare @comTemplate varchar(max) =
where ROUTINE_NAME = ''###pName''  and ROUTINE_SCHEMA = ''###sch'' ',
@com varchar(max),
@sourceHashedVal varbinary(4000), @targetHashedVal varbinary(4000)
set @com = Replace(Replace(Replace(Replace(@comTemplate, '###Db', @TargetDbName),'###sch',@TargetSchema),'###pName',@ProgrammableName), '###srvr', @trgServerName)
 insert into @RawRoutineRow exec(@com)
set @targetHashedVal = (select HashBytes('SHA1',routineDef)
   from @RawRoutineRow)


Thank you! Natali

Tuesday, March 20, 2012 - 2:35:25 PM - Burt Back To Top (16545)

I originally started with checksum but as a coworker pointed out it does have a higher degree of error than hashbytes.... Here's the MSFT article which suggests against using checksum , " unless your application can tolerate occasionally missing a change."





Tuesday, March 20, 2012 - 1:35:26 PM - Ronald Dameron Back To Top (16544)

Ola Hallengren uses the CHECKSUM function in the following way to verify that his SQL Server maintenance  solution is the same across all servers.

A multi-server query and the following with the IN list modified with the relevant object(s) should get the task done also.


SELECT SUM(CAST(CHECKSUM(CAST(OBJECT_DEFINITION([object_id]) AS nvarchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS bigint)) AS ObjectChecksum
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id]
WHERE = 'dbo'
AND IN('CommandExecute','DatabaseBackup','DatabaseIntegrityCheck','DatabaseSelect','IndexOptimize')


Tuesday, March 20, 2012 - 1:03:25 PM - Hendrik Back To Top (16542)

We use Red Gate tools for this purpose, No coding etc.

get free sql tips
agree to terms