zachtbeer.SqlSchemaHasher 1.0.0

zachtbeer.SqlSchemaHasher

A lightweight .NET library for computing deterministic SHA256 hashes of SQL Server database schemas. Use it to detect schema changes, group databases by schema signature, or verify schema consistency across environments.

Why Schema Hashing?

The Problem

When you manage many SQL Server databases, schemas drift. Someone modifies a table directly. A backup gets restored from the wrong date. A migration partially applies and nobody notices. Without a fast way to answer "what schema is this database actually running?", drift is invisible until something breaks.

Why Hashing Instead of Version Numbers

Version numbers require discipline: someone has to bump them, and they have to be applied everywhere consistently. More importantly, version numbers tell you what should be deployed, not what actually is deployed. They can lie. Migration journals track what scripts ran, not what the schema looks like after manual changes.

A content-based hash is computed from the schema itself -- it's ground truth. Two databases with the same hash are structurally identical, period. No trust required, no process dependency, no "well it should be on v4.2."

Hashes also surface unexpected groupings. You might discover that 300 databases are on schema A, 50 are on schema B, and 3 are on something nobody recognizes. That visibility alone saves you from a class of problems that have historically been expensive to diagnose.

How You Might Use It

• Define the golden schema -- Hash a reference database and compare everything against it. Any mismatch is immediately actionable.
• Continuous drift monitoring -- Compute schema hashes on a schedule and report them centrally. Deviations surface in dashboards instead of in support tickets.
• Migration tooling -- Compare a database's hash against the target schema before generating diff SQL. If hashes match, skip the diff entirely.
• Fleet-wide grouping -- Group all databases by hash to get the full picture of what's actually deployed.

Installation

dotnet add package zachtbeer.SqlSchemaHasher

Quick Start

using zachtbeer.SqlSchemaHasher;

// Simple one-liner to get a schema hash
var hash = await SqlSchemaHash.GetHashAsync("Server=localhost;Database=MyDb;Trusted_Connection=true");
// Returns: "dGhpcyBpcyBhIGJhc2U2NCBoYXNo..."

API Reference

SqlSchemaHash.GetHashAsync(connectionString)

Returns a base64-encoded SHA256 hash of the database schema.

var hash = await SqlSchemaHash.GetHashAsync(connectionString);

SqlSchemaHash.GetHashAsync(connectionString, options)

Returns a hash with custom normalization options.

var options = new SchemaHashOptions
{
    NormalizeAutoGeneratedIndexNames = true,  // Strip GUID suffixes from index names
    NormalizeClusteringType = true            // Treat clustered/nonclustered as equivalent
};

var hash = await SqlSchemaHash.GetHashAsync(connectionString, options);

SqlSchemaHash.ExtractSchemaAsync(connectionString)

Returns detailed schema metadata if you need to inspect the schema structure.

var schema = await SqlSchemaHash.ExtractSchemaAsync(connectionString);

Console.WriteLine($"Tables: {schema.Tables.Count}");
Console.WriteLine($"Stored Procedures: {schema.StoredProcedures.Count}");
Console.WriteLine($"User-Defined Types: {schema.UserDefinedTableTypes.Count}");

SqlSchemaHash.ComputeHash(schema, options)

Computes a hash from pre-extracted schema metadata. Useful when comparing the same schema with different normalization options.

var schema = await SqlSchemaHash.ExtractSchemaAsync(connectionString);

var strictHash = SqlSchemaHash.ComputeHash(schema);
var normalizedHash = SqlSchemaHash.ComputeHash(schema, new SchemaHashOptions
{
    NormalizeAutoGeneratedIndexNames = true
});

What Gets Hashed

The hash includes:

• Tables: Schema name, table name, columns (name, type, precision, nullability), indexes, constraints, identity columns
• Stored Procedures: Schema name, procedure name, parameters, and a hash of the procedure body (detects logic changes)
• User-Defined Table Types: Schema name, type name, columns

Excluded Objects

The following objects are automatically excluded from hashing (via SchemaHashOptions.Default):

• sysdiagrams table and related diagram helper procedures (fn_diagramobjects, sp_alterdiagram, sp_creatediagram, sp_dropdiagram, sp_helpdiagramdefinition, sp_helpdiagrams, sp_renamediagram)

Options

Use Cases

Hash Only Specific Schema

// Only hash objects in the dbo schema, ignoring other schemas like reporting or staging
var options = new SchemaHashOptions { SchemaFilter = "dbo" };
var hash = await SqlSchemaHash.GetHashAsync(connectionString, options);

Schema Change Detection

var beforeHash = await SqlSchemaHash.GetHashAsync(connectionString);
// ... apply migration ...
var afterHash = await SqlSchemaHash.GetHashAsync(connectionString);

if (beforeHash != afterHash)
{
    Console.WriteLine("Schema changed!");
}

Group Databases by Schema

var databases = new[] { "Db1", "Db2", "Db3", "Db4" };
var groups = new Dictionary<string, List<string>>();

foreach (var db in databases)
{
    var connStr = $"Server=localhost;Database={db};Trusted_Connection=true";
    var hash = await SqlSchemaHash.GetHashAsync(connStr);

    if (!groups.ContainsKey(hash))
        groups[hash] = new List<string>();
    groups[hash].Add(db);
}

// Databases with identical schemas are grouped together

Environment Verification

var prodHash = await SqlSchemaHash.GetHashAsync(prodConnectionString);
var stagingHash = await SqlSchemaHash.GetHashAsync(stagingConnectionString);

if (prodHash != stagingHash)
{
    throw new Exception("Staging schema does not match production!");
}

Dependencies

• Microsoft.Data.SqlClient - SQL Server connectivity
• Dapper - Efficient database queries

License

MIT