Wednesday, February 26, 2020

SERVERPROPERTY for SQL Server version and build details

Say you have a large inventory of servers, and you need to query them all to confirm version and see what service patching may be needed.  Well here you go.  This is a very simple query that you need to run only once on a CMS to target many servers at the same time.  

Simple statement: 

-- query serveroperty for version and build details

SELECT
  CASE
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'    
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
     ELSE 'UNKNONWN' END MajorVersion,
   SERVERPROPERTY('ProductLevel') ProductLevel,
   SERVERPROPERTY('Edition') Edition,
   SERVERPROPERTY('ProductVersion') ProductVersion
ORDER BY 1;

Your output will be a little something like this:












Note, you don't need to put a @@SERVERNAME into your SELECT because the CMS will return it back to you from every server registered within the CMS.  If you haven't used the CMS before, please check out the link below.  Definitely makes life easier when you need to run the same statement across multiple servers.  

https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group?view=sql-server-ver15

No comments:

Post a Comment