Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Verify SQL Server Stored Procedures are the same on multiple servers


By:   |   Last Updated: 2012-03-20   |   Comments (15)   |   Related Tips: More > 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.

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


Last Updated: 2012-03-20


next webcast button


next tip button



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 MSSQLTips.com since 2011.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 Hi,

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. 

Br

Gerald


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

This hash function doesn't have the size limit:

master.sys.fn_repl_hash_binary(content)

 


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

Thanks, this was really useful to me today


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

Does anyone has an updated script for SQL 2000 ?


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

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

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

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 09, 2012 - 5:14:12 PM - Jorge Serres Back To Top

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 patch...re 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 09, 2012 - 11:08:26 AM - Dave Winters Back To Top

 

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.

 

Dave

http://theseldonvault.blogspot.com/2010/02/sign-sql-stored-procedure-to-track.html


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

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

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

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) =
'select ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME,ROUTINE_DEFINITION, LAST_ALTERED from [###srvr].[###Db].INFORMATION_SCHEMA.ROUTINES 
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

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

http://msdn.microsoft.com/en-us/library/ms189788.aspx

 

-Cheers

 

 


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

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

http://ola.hallengren.com/scripts/ObjectChecksum.sql

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

SET NOCOUNT ON

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 sys.schemas.name = 'dbo'
AND sys.objects.name IN('CommandExecute','DatabaseBackup','DatabaseIntegrityCheck','DatabaseSelect','IndexOptimize')

 


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

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


Learn more about SQL Server tools