Sql-server – ny way to run the definition of multiple stored procedure at a time

We recently had this discussion at my workplace.

First, I want to commend you for doing the "right thing" by using HASHBYTES() over CHECKSUM() to detect changes. Microsoft specifically cautions against using CHECKSUM(@input) for this purpose as its collision rate is very high compared to that of HASHBYTES('SHA1', @input). One advantage CHECKSUM() does have, though, is that there is no (obvious) restriction on the size of its input.

Second, if you use HASHBYTES() I recommend using SHA1 as your hash algorithm. Of the available options SHA1 has the lowest collision rate, and speed is not a concern for your use case.

Finally, To use HASHBYTES() against inputs larger than 8000 bytes you'll have to:

1. Split your input into 8000 byte chunks.
2. Hash each chunk.

3. Somehow combine the resulting hashes and hash them to get your final output.

   You can do this in one of two ways:

   1. Convert your hash outputs into strings, concatenate them, and hash the result.
   2. Stick all your hash outputs into a memory table and take their aggregate checksum using CHECKSUM_AGG().

4. Encapsulate this work as a function that takes NVARCHAR(MAX) as its input.

All that said, it is all-around simpler to just compare the proc definitions directly using OBJECT_DEFINITION() as gbn suggested, or to simply push all definitions out everywhere as often as you like, as Mike suggested.

I wonder what kind of environment would significantly benefit from a process that deployed only changed procedures and used hashes to avoid copying around and comparing full definitions. You'd need to have a lot of procedures to keep in sync.

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.