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:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| ProductVersion | nvarchar(128) | Whole build number string. |
| MajorProductVersion | nvarchar(4) | Only major version value. |
| MinorProductVersion | nvarchar(4) | Minor product version (appearing in SQL Server 2008 R2). |
| ServicePackLevel | nvarchar(4) | Update level of your SQL Server. You can tell which SPs or CUs are installed based on this number. |