HASHBYTES() result of another HASHBYTES(), OR CHECKSUM_AGG() aggregate for HASHBYTES()?

Question

I want to aggregate a single HASHBYTES() from the results of another HASHBYTES()... something like CHECKSUM_AGG() does for CHECKSUM()...

I have been able find how to make a single HASH per row of columns from a table:

SELECT 
    hashbytes('MD5', (
            SELECT SPECIFIC_SCHEMA
                ,SPECIFIC_NAME
                ,ROUTINE_SCHEMA
                ,ROUTINE_NAME
                ,ROUTINE_TYPE
                ,ROUTINE_DEFINITION
            FROM (
                VALUES (NULL)
                ) foo(bar)
            FOR XML auto
            )) AS [Hash]
FROM INFORMATION_SCHEMA.ROUTINES AS MBT;

How do I aggregate to a single hash from all the hash rows returned?

(Yes, I want to reduce a table to a single hash for comparison to table(s) in other databases).

Answer 1

You can use CONCAT to concatenate all the columns per row, then STRING_AGG to concatenate the whole table, and hash the result.

How reliable such a hash would be in the face of collisions, I couldn't say. MD5 is deprecated anyway, you should use SHA2_512

SELECT 
    HASHBYTES('SHA2_512',
      STRING_AGG(CONCAT(
        CAST(SPECIFIC_SCHEMA AS nvarchar(max))  -- at least one '(max)'
       ,SPECIFIC_NAME
       ,ROUTINE_SCHEMA
       ,ROUTINE_NAME
       ,ROUTINE_TYPE
       ,ROUTINE_DEFINITION
       ), '')) AS [hash]
FROM INFORMATION_SCHEMA.ROUTINES AS MBT;

db<>fiddle

Why you are using INFORMATION_SCHEMA.ROUTINES as opposed to sys.procedures is a different question. Is it just an example?