T-SQL – Checking SQL Server Version and Service Packs

Often, you will need to check for SQL Server versions and service packs. Some of the features that you are using are only available from a service pack onward.

Microsoft provides the function server property to query for that kind of information.

SELECT
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
AS VARCHAR(17)) AS 'Computer physical name',
CAST(SERVERPROPERTY('ServerName')
AS VARCHAR(17)) AS 'Server name',
CAST(SERVERPROPERTY('MachineName')
AS VARCHAR(17)) AS 'Machine name',
CAST(SERVERPROPERTY('Edition')
AS
VARCHAR(25)) AS 'Server edition',
CAST(SERVERPROPERTY('InstanceName')
AS VARCHAR(17)) AS 'Instance name',
CAST(SERVERPROPERTY('LicenseType')
AS VARCHAR(15)) AS 'License type',
CAST(SERVERPROPERTY('ProductVersion')
AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel')
AS VARCHAR(15)) AS 'Service pack';

Go