Spotting Definition Drift With HASHBYTES on Module Code

Definition drift is when a stored procedure or view in production quietly stops matching the one you approved. You can catch it by hashing the exact module text and comparing it with a saved baseline. The hash tells you that something changed. It does not tell you what.

Why hash the module text

Here is a call I have taken more than once. A report returns different numbers in production than in test. Both servers were deployed from the same script. Someone says nobody touched it. Someone else remembers an emergency fix on a Friday night.

You could open both procedures and read them line by line. With 400 procedures, that is a long afternoon. A hash does the first pass for you. SQL Server turns the full module text into a short fingerprint. Same text gives the same fingerprint. Change one character and the fingerprint changes completely.

The catch is simple. A fingerprint is only useful next to something you saved earlier. So the plan has two steps: save a baseline now, compare against it later.

Save a baseline with the definition

The demo creates four small procedures. One of them is encrypted, which matters in a minute. Then it stores a baseline in a temp table: schema, name, type, the full definition and its SHA2_256 hash. I keep the definition text too, because a bare hash cannot show you what changed.

DROP PROCEDURE IF EXISTS dbo.GetPriceNote, dbo.GetStockNote, dbo.OldReport, dbo.NewReport, dbo.SecretRule;
GO
CREATE PROCEDURE dbo.GetPriceNote AS SELECT N'before' AS ValueText;
GO
CREATE PROCEDURE dbo.GetStockNote AS SELECT N'steady' AS ValueText;
GO
CREATE PROCEDURE dbo.OldReport AS SELECT N'retired soon' AS ValueText;
GO
CREATE PROCEDURE dbo.SecretRule WITH ENCRYPTION AS SELECT N'hidden' AS ValueText;
GO
DROP TABLE IF EXISTS #ModuleBaseline;

SELECT s.name AS schema_name, o.name AS object_name, o.type_desc,
       m.definition, HASHBYTES('SHA2_256', m.definition) AS definition_hash
INTO #ModuleBaseline
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0;

SELECT object_name, DATALENGTH(definition_hash) AS hash_bytes, definition
FROM #ModuleBaseline
ORDER BY object_name;

The last result shows four rows. Every hash is 32 bytes, which is the size of SHA2_256. Look at SecretRule. Its definition is NULL, and so is its hash. SQL Server will not hand over the text of an encrypted module, so there is nothing to fingerprint.

Change things and compare

Now play the Friday night fix. One procedure gets altered, one is dropped, and one new procedure appears. The comparison joins the baseline to the current catalog with a FULL OUTER JOIN. That matters, because an inner join would silently forget the dropped and the new objects.

ALTER PROCEDURE dbo.GetPriceNote AS SELECT N'after' AS ValueText;
GO
DROP PROCEDURE dbo.OldReport;
GO
CREATE PROCEDURE dbo.NewReport AS SELECT N'brand new' AS ValueText;
GO
WITH CurrentModules AS (
    SELECT s.name AS schema_name, o.name AS object_name, m.definition
    FROM sys.sql_modules AS m
    JOIN sys.objects AS o ON o.object_id = m.object_id
    JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    WHERE o.is_ms_shipped = 0
)
SELECT COALESCE(b.object_name, c.object_name) AS object_name,
       CASE WHEN b.object_name IS NULL THEN 'Added'
            WHEN c.object_name IS NULL THEN 'Missing'
            WHEN b.definition IS NULL OR c.definition IS NULL THEN 'Unreadable'
            WHEN b.definition_hash = HASHBYTES('SHA2_256', c.definition) THEN 'Same'
            ELSE 'Changed' END AS comparison_status,
       b.definition AS baseline_definition, c.definition AS current_definition
FROM #ModuleBaseline AS b
FULL OUTER JOIN CurrentModules AS c
  ON c.schema_name = b.schema_name AND c.object_name = b.object_name
ORDER BY object_name;

You get five rows. GetPriceNote says Changed, and both definitions sit side by side, so the difference is easy to see. GetStockNote says Same. OldReport says Missing. NewReport says Added. SecretRule says Unreadable.

That last label is the one people skip. If the text is NULL, a comparison proves nothing. Calling it Same would be a lie, and calling it Changed would be a false alarm. Give it its own status and look at it by hand.

Why one space is a different hash

Hashing compares text, not meaning. Two statements that do the same job but differ by one space produce different hashes.

SELECT CASE WHEN HASHBYTES('SHA2_256', N'SELECT 1;') =
                 HASHBYTES('SHA2_256', N'SELECT  1;')
            THEN 1 ELSE 0 END AS whitespace_hashes_match;

The answer is 0. For drift detection, that is what you want. A formatting tool that reflows every procedure will light up your report, and that is a fair thing to see. Do not normalize the text to hide it. Normalizing can also hide a real change inside a string or a piece of dynamic SQL.

What to do with a Changed row

Do not rush to restore the old version. The change may be an accepted emergency fix that someone forgot to record. Read the two definitions first, then decide. If you do restore, check permissions, signing and anything that depends on the module.

Run the same collector under the same permissions each time. If one run has less metadata access than the last, objects vanish from the inventory. You then get a wave of Missing rows that mean nothing. The last block cleans up the demo.

DROP PROCEDURE IF EXISTS dbo.GetPriceNote, dbo.GetStockNote, dbo.NewReport, dbo.SecretRule;
DROP TABLE IF EXISTS #ModuleBaseline;

Save a baseline after your next deployment, and the next odd report gets a lot less mysterious.

A definition hash is not an explanation, it is a signal that the text differs.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.