How to compare stored procedures in different databases/servers to confirm they are the same, without third party tools or Diff's?
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.
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.
Wrapping it up
There are some points mentioned above, but worth noting here too.
- The HashBytes function will not accept enormous sizes; review the Microsoft link for more information but be prepared to do some tweaking.
- 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.
- 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.
- 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
This looks like it could come in handy. I dig the idea of hashing the SP's and then using that hash. So obvious in hindsight (especially since I use this same kind of technique in a bunch of other places... I just never considered using it this way... )