Verify SQL Server Stored Procedures are the same on multiple servers

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

Problem

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.

Solution

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.

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.

(SELECT OBJECT_DEFINITION (OBJECT_ID('sprocname_here' ))) 

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.

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
    begin
        select 'This server is ok'
    end
else
    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.

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

• Take a look at the OBJECT_DEFINITION function: Link to Microsoft
• Review documentation from Microsoft on HashBytes here: Link to Microsoft
• Check out the MSSQLTIPS piece on querying multiple servers at the same time: MSSQLTIPS article

About the author

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

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

View all my tips