Part of the DBA-Tools Project.
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 exactproduct_versionbuild number is what you need to cross-reference against a patch tracking site like sqlserverupdates.com 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_referencegives 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.
*/
-- Blog: https://sqldba.blog/dba-scripts-get-patch-level/
-- 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'
WHEN 4 THEN 'Express'
WHEN 5 THEN 'SQL Database (Azure)'
WHEN 6 THEN 'Azure Synapse'
WHEN 7 THEN 'Managed Instance'
WHEN 8 THEN 'Developer'
ELSE 'Unknown'
END AS engine_edition,
CAST(SERVERPROPERTY('ResourceLastUpdateDateTime') AS datetime) AS resource_db_updated,
CAST(SERVERPROPERTY('BuildClrVersion') AS varchar(20)) AS clr_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'
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:
Example Output
Real output from this lab instance, not staged:
| server_name | product_version | version_friendly | product_update_level | kb_reference | edition | patch_summary |
|---|---|---|---|---|---|---|
| HPAI01 | 17.0.4045.5 | SQL Server 2025 | CU5 | KB5084896 | Enterprise Developer Edition (64-bit) | SQL Server 2025 CU5 (17.0.4045.5) |
Understanding the Results
- version_friendly — the human-readable major version, resolved from the raw build number so you don’t have to memorize which major build number maps to which release
- product_update_level — the CU number. Compare this against the latest available CU for the major version to see how far behind an instance is
- kb_reference — the exact Microsoft KB article for the currently installed update, useful for checking exactly what a given patch level fixed
- engine_edition — the numeric edition family (Standard, Enterprise, Managed Instance, etc.), useful for filtering an estate-wide inventory by edition without parsing the free-text
editionstring
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_versionagainst 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
Related Scripts
You may also find these scripts useful:
- Server Inventory (hub)
- Version and Edition
- OS and Hardware Info
- Edition Feature Usage
- Database Snapshot Inventory
- Login and Job Inventory
Frequently Asked Questions
How is this different from Version and Edition?
Version and Edition is a quick single-server snapshot, core version, edition, cluster status. This script is built for scale, run it across an entire estate and the friendly version name plus KB reference turn the results directly into a patch compliance report.
Does this tell me if I’m missing a security patch?
It tells you exactly what’s installed. Whether that’s current requires comparing product_version against Microsoft’s own release history, this script gives you the precise, unambiguous number to check.
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.
Leave a Reply