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.

Check out this question: http://stackoverflow.com/questions/7587077/how-do-i-check-for-the-sql-server-version-using-powershell — Paul Andrew, Jul 13 '16 at 07:57

score 6 · Answer 1 · answered Jul 13 '16 at 06:04

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];

Thanks for quick response :) SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], 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 This value are NULL also service-pack and patches are missing. :( — Sachin Tiwari, Jul 13 '16 at 06:15
Thank you for your response there is no service pack installed on server can you help in getting patches for the server — Sachin Tiwari, Jul 13 '16 at 06:43

score 1 · Answer 2 · answered Mar 19 '18 at 17:01

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]

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

2 Answers2