DBA Scripts: Get Patch Level

Building a Patch Compliance Inventory Across an Estate

Version and Edition answers “what is this one instance running” as a quick single-server snapshot. This script answers a related but different question: run it across every server you manage and you get a patch compliance inventory, ready to compare against what’s actually current, one row per server, with the friendly version name and the exact KB reference already resolved.

If you’re checking one server, either script tells you what you need. If you’re tracking patch compliance across an estate, this is the one built for that.

Why Patch Level Matters

• A friendly version name (SQL Server 2019) plus the exact product_version build number is what you need to cross-reference against a patch tracking site like sqlserverupdates.com, and the SQL Server builds reference here tracks the latest CU and build per version
• product_update_level (the CU number) is the single fastest way to see whether a server is behind on cumulative updates without decoding a raw build number
• Run across every instance in an estate, this becomes a compliance report: which servers are current, which are stuck on an old CU, which need scheduling for the next patch window
• kb_reference gives you the exact article to check for what that specific update actually fixed, useful when deciding whether an unpatched CVE applies

When to Run This Script

• Building or refreshing a patch compliance inventory across a server estate
• Before deciding whether a specific CVE or bug fix applies to an instance, cross-referencing the exact build against Microsoft’s release notes
• Ahead of a migration or upgrade project, to know the real starting point for every server involved
• Routine SQL Server health checks, alongside OS and Hardware Info

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-PatchLevel
Category    : monitoring
Purpose     : Reports SQL Server version, Cumulative Update level, edition, and build
              number for patch-level tracking across an estate.
              Run on each server to build a patch compliance inventory.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-patch-level/)
Requires    : Public (no elevated permissions required)
Notes       : product_update_level returns the CU number (e.g. CU12) on SQL 2012+.
              For SQL 2012 RTM this column may be NULL.
              Compare product_version against https://sqlserverupdates.com to determine
              whether the instance is on the latest CU for its major version.
              build_clr_version is included because CLR version changes affect assembly
              compatibility during upgrades.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @productVersion   varchar(20) = CAST(SERVERPROPERTY('ProductVersion')         AS varchar(20));
DECLARE @majorVer         int         = CAST(PARSENAME(@productVersion, 4)             AS int);

SELECT
    @@SERVERNAME                                                                AS server_name,
    @productVersion                                                             AS product_version,

    CASE @majorVer
        WHEN 17 THEN 'SQL Server 2025'
        WHEN 16 THEN 'SQL Server 2022'
        WHEN 15 THEN 'SQL Server 2019'
        WHEN 14 THEN 'SQL Server 2017'
        WHEN 13 THEN 'SQL Server 2016'
        WHEN 12 THEN 'SQL Server 2014'
        WHEN 11 THEN 'SQL Server 2012'
        ELSE          'SQL Server (ver ' + CAST(@majorVer AS varchar(5)) + ')'
    END                                                                         AS version_friendly,

    CAST(SERVERPROPERTY('ProductLevel')         AS varchar(20))                 AS product_level,
    CAST(SERVERPROPERTY('ProductUpdateLevel')   AS varchar(20))                 AS product_update_level,
    CAST(SERVERPROPERTY('ProductUpdateReference') AS varchar(30))               AS kb_reference,
    CAST(SERVERPROPERTY('Edition')              AS varchar(128))                AS edition,

    CASE CAST(SERVERPROPERTY('EngineEdition') AS int)
        WHEN 1  THEN 'Personal/Desktop'
        WHEN 2  THEN 'Standard'
        WHEN 3  THEN 'Enterprise' -- includes Developer and Evaluation
        WHEN 4  THEN 'Express'
        WHEN 5  THEN 'SQL Database (Azure)'
        WHEN 6  THEN 'Azure Synapse Analytics'
        WHEN 8  THEN 'Azure SQL Managed Instance'
        WHEN 9  THEN 'Azure SQL Edge'
        WHEN 11 THEN 'Azure Synapse serverless SQL pool'
        ELSE        'Unknown'
    END                                                                         AS engine_edition,

    CAST(SERVERPROPERTY('ResourceLastUpdateDateTime') AS datetime)              AS resource_db_updated,
    CAST(SERVERPROPERTY('BuildClrVersion')      AS varchar(20))                 AS clr_version,

    -- Friendly patch summary: e.g. "SQL Server 2019 CU12 (15.0.4153.1)"
    CASE @majorVer
        WHEN 17 THEN 'SQL Server 2025'
        WHEN 16 THEN 'SQL Server 2022'
        WHEN 15 THEN 'SQL Server 2019'
        WHEN 14 THEN 'SQL Server 2017'
        WHEN 13 THEN 'SQL Server 2016'
        WHEN 12 THEN 'SQL Server 2014'
        WHEN 11 THEN 'SQL Server 2012'
        ELSE          'SQL Server'
    END
    + ' '
    + ISNULL(CAST(SERVERPROPERTY('ProductUpdateLevel') AS varchar(20)),
             CAST(SERVERPROPERTY('ProductLevel') AS varchar(20)))
    + ' (' + @productVersion + ')'                                             AS patch_summary;

The patch_summary column is deliberately built as one readable string, “SQL Server 2019 CU12 (15.0.4153.1)”, the format you’d actually paste into a compliance spreadsheet or a ticket.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Get patch level and version detail for one instance:
.\run.ps1 Get-PatchLevel

# To run against a remote sql server:
.\run.ps1 Get-PatchLevel -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/inventory/Get-PatchLevel.sql
• powershell/wrappers/inventory/Get-PatchLevel.ps1

Example Output

One row per instance. Run it on each server and stack the rows to get a patch inventory for the estate, which is the job this script exists for.

Understanding the Results

Best Practices

• Run this across every server in an estate on a schedule, not just when a patch is being considered, a compliance inventory is only useful if it’s current
• Cross-reference product_version against a patch tracking site to determine whether each instance is on the latest CU for its major version, this script tells you what’s installed, not what’s current
• Pair with OS and Hardware Info when building a full server inventory, patch level plus hardware context covers most of what a migration or capacity project needs upfront

Microsoft’s reference covers SERVERPROPERTY in full, including every property this script reads and the versions each one is available on.

Related Scripts

You may also find these scripts useful:

• SQL Server Builds and Support Lifecycle, the reference to check any build number against
• Patch SQL Server, the one-shot script for when this output says a server is behind
• Server Inventory (hub)
• Version and Edition
• OS and Hardware Info
• Edition Feature Usage
• Database Snapshot Inventory
• Login and Job Inventory
• Database Inventory
• Database Summary
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

Patch compliance across an estate starts with knowing, precisely and consistently, what every server is actually running, not an approximate “it’s on 2019 I think.” This script resolves the raw version number into a friendly name, a CU level, and a KB reference in one readable row, built to run across many servers, not just one.

Run it as part of routine health checks across every instance you manage, and keep the results current, a patch inventory that’s months out of date is only slightly better than none at all.