Knowing your SQL Server

  1. Server Name
  2. KB Version (This is optional)
  3. BuildVersion — Since not everyone know what each build version means, we need to convert it into meaningful name. This comes in handy when creating reports for upper management.
  4. ServicePack and Cumulative Update (This is good to know and audit to make sure that all your servers are on same level if need to be -unless there are exceptions)
Select @@SERVERNAME ,SUBSTRING(@@VERSION,(PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@@version)),9) as KBVersion ,ServerProperty('ProductVersion') as BuildVersion ,CASE WHEN LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='10.0' THEN 'SQL Server 2008' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='10.5' THEN 'SQL Server 2008 R2' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='11.0' THEN 'SQL Server 2012' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='12.0' THEN 'SQL Server 2014' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='13.0' THEN 'SQL Server 2016' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='14.0' THEN 'SQL Server 2017' ELSE SERVERPROPERTY ('ProductVersion') END AS SQLServerVersion ,SERVERPROPERTY('ProductLevel') as ServicePack ,SERVERPROPERTY ('ProductUpdateLevel') as CumulativeUpdate

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sanchit Wadhwa

Sanchit Wadhwa

I help solve data problems |Need help ? email me > imsunchip@gmail.com | website > sanchitwadhwa.com | ❤ Books, music, dance, food, podcasts, soccer, writing