One of the first things we do when inheriting a new SQL Server environment or when troubleshooting issues, knowing SQL Server version. There are different ways to get SQL Server information. Here is a script to find SQL Server version information.
The @@VERSION returns the SQL Server build information for the current installation of SQL Server. The SQL version from the above snapshot is SQL Server 2014 Enterprise Edition with build number 12.0.2000. It also returns the windows version the SQL server is installed on.
Another way is to use the SERVERPROPERTY function as shown below.
SELECT SERVERPROPERTY('productversion') AS SQLProductVersion,
SERVERPROPERTY('productlevel') AS SQLProductLevel,
SERVERPROPERTY('edition') AS SQLEdition
The Product Version returned by SERVERPROPERTY function can be formatted to return the SQL Server version as shown below.
SELECT Serverproperty('productversion') AS SQLProductVersion,
CASE Substring(Cast(Serverproperty('ProductVersion') AS NVARCHAR), 1,
Charindex('.', Cast(Serverproperty('ProductVersion') AS
NVARCHAR)) - 1
)
WHEN '7' THEN 'SQL Server 7'
WHEN '8' THEN 'SQL Server 2000'
WHEN '9' THEN 'SQL Server 2005'
WHEN '10' THEN 'SQL Server 2008/2008R2'
WHEN '11' THEN 'SQL Server 2012'
WHEN '12' THEN 'SQL Server 2014'
ELSE 'Unknown'
END AS SQLServerProduct,
Serverproperty('productlevel') AS SQLProductLevel,
Serverproperty('edition') AS SQLEdition
The above query can be used to make inventory of all the SQL Server version in an environment for monitoring or patching purpose.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
