SQL Server - Build number cut into parts

Identify build number of your SQL Server cut into parts. You can use it in scripting or in automation use cases. More information about build numbers. This piece of code can also help you to identify missing updates among more instances when used in multi-server query using registered servers.

I am using this nice website when looking for service pack or cumulative update build number – buildnumbers.wordpress.com. It contains information about more Microsoft products as well.

DECLARE @ProductVersion	NVARCHAR(128)
DECLARE @MajorProductVersion NVARCHAR(4)
DECLARE @MinorProductVersion NVARCHAR(4)
DECLARE @ServicePackLevel NVARCHAR(4)

SET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS nvarchar)
SET @MajorProductVersion = SUBSTRING(@ProductVersion, 1, CHARINDEX('.', @ProductVersion) - 1)
SET @MinorProductVersion = SUBSTRING(@ProductVersion, CHARINDEX('.', @ProductVersion) + 1  , 1)
SET @ServicePackLevel = SUBSTRING(
	@ProductVersion, -- expresion
	CHARINDEX( '.', @ProductVersion, CHARINDEX( '.', @ProductVersion ) + 1 ) + 1, -- starting index
	CHARINDEX( '.', @ProductVersion, CHARINDEX( '.', @ProductVersion ) + 4) - CHARINDEX( '.', @ProductVersion, CHARINDEX( '.', @ProductVersion ) + 1 ) -- count of chars
)

SELECT 
	@ProductVersion AS ProductVersion, 
	@MajorProductVersion AS MajorProductVersion, 
	@MinorProductVersion AS MinorProductVersion, 
	@ServicePackLevel AS ServicePackLevel

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
ProductVersionnvarchar(128)Whole build number string.
MajorProductVersionnvarchar(4)Only major version value.
MinorProductVersionnvarchar(4)Minor product version (appearing in SQL Server 2008 R2).
ServicePackLevelnvarchar(4)Update level of your SQL Server. You can tell which SPs or CUs are installed based on this number.

Leave a Reply

Your email address will not be published. Required fields are marked *