Verify SQL Server Stored Procedures are the same on multiple servers

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.

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:

(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.

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

    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.

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

Related Posts

2 Comments

  1. ——-this script to check for all database to find hashed value—-

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

    DECLARE @sql NVARCHAR(MAX)

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

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE state_desc = ‘ONLINE’ — Consider only online databases

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT ‘Database: ‘ + @dbname

    SET @sql = ‘
    USE [‘ + @dbname + ‘];
    DECLARE sp_cursor CURSOR FOR
    SELECT name
    FROM sys.procedures;
    OPEN sp_cursor;
    FETCH NEXT FROM sp_cursor INTO @spname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    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;
    END
    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
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    SELECT * FROM #HashedValues

    DROP TABLE #HashedValues

  2. Hi,

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *