How do I verify SQL Server versions, Including version, service pack, cumulative update and patch

Question

I have 250 VM's for different clients using different version of SQL server installed.

I ran the below command to get the details but the information was not successful

SELECT 
    SERVERPROPERTY('productversion'), 
    SERVERPROPERTY('productlevel'),
    SERVERPROPERTY ('edition'),  
    @@version, 
    SERVERPROPERTY('PatchLevel')

Can someone please help? I need the information for details like SQL Server version, service pack, cumulative update and patches installed to the server.

Answer 1

Hope you need this.,

select @@VERSION

Hope this helps you.,

SELECT SERVERPROPERTY('MachineName') AS [MachineName], 
SERVERPROPERTY('ServerName') AS [ServerName],  
SERVERPROPERTY('InstanceName') AS [Instance], 
SERVERPROPERTY('IsClustered') AS [IsClustered], 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 
SERVERPROPERTY('Edition') AS [Edition], 
SERVERPROPERTY('ProductLevel') AS [ProductLevel],               -- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],   -- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion], 
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion], 
SERVERPROPERTY('ProductBuild') AS [ProductBuild], 
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],       -- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], 
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], 
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];

Answer 2

Here is a query that gives you all the Server Properties available through the SERVERPROPERTY system function.

You can run this on old SQL versions and if the Property didn't exist back then, the function will just safely return a NULL.

SELECT
     SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion
    ,SERVERPROPERTY('Collation') AS [Collation]
    ,SERVERPROPERTY('CollationID') AS CollationID
    ,SERVERPROPERTY('ComparisonStyle') AS ComparisonStyle
    ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS]
    ,SERVERPROPERTY('Edition') AS [Edition]
    ,SERVERPROPERTY('EditionID') AS EditionID
    ,SERVERPROPERTY('EngineEdition') AS EngineEdition
    ,SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel
    ,SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel
    ,SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName
    ,SERVERPROPERTY('HadrManagerStatus') AS HadrManagerStatus
    ,SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath
    ,SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath
    ,SERVERPROPERTY('InstanceName') AS [Instance]
    ,SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled
    ,SERVERPROPERTY('IsClustered') AS [IsClustered]
    ,SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
    ,SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled
    ,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly]
    ,SERVERPROPERTY('IsLocalDB') AS IsLocalDB
    ,SERVERPROPERTY('IsPolybaseInstalled') AS IsPolybaseInstalled
    ,SERVERPROPERTY('IsSingleUser') AS IsSingleUser
    ,SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported
    ,SERVERPROPERTY('LCID') AS LCID
    ,SERVERPROPERTY('LicenseType') AS LicenseType
    ,SERVERPROPERTY('MachineName') AS [MachineName]
    ,SERVERPROPERTY('ProcessID') AS [ProcessID]
    ,SERVERPROPERTY('ProductBuild') AS ProductBuild
    ,SERVERPROPERTY('ProductBuildType') AS ProductBuildType
    ,SERVERPROPERTY('ProductLevel') AS ProductLevel
    ,SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion
    ,SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion
    ,SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel
    ,SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference
    ,SERVERPROPERTY('ProductVersion') AS [ProductVersion]
    ,SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime
    ,SERVERPROPERTY('ResourceVersion') AS ResourceVersion
    ,SERVERPROPERTY('ServerName') AS [ServerName]
    ,SERVERPROPERTY('SqlCharSet') AS SqlCharSet
    ,SERVERPROPERTY('SqlCharSetName') AS SqlCharSetName
    ,SERVERPROPERTY('SqlSortOrder') AS SqlSortOrder
    ,SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName
    ,@@VERSION AS [Version]