How do you check what version of SQL Server for a database using TSQL

Question

Is there a system stored procedure to get the version

User · Answer

SELECT   VERSION

User · Answer

There is another extended Stored Procedure which can be used to see the Version info   exec  master  sys  xp msver

User · Answer

Try this   SELECT      the sqlserver is     substring   VERSION  21  5  AS  sql version

User · Answer

Try   SELECT   VERSION    or for SQL Server 2000 and above the following is easier to parse      SELECT SERVERPROPERTY  productversion          SERVERPROPERTY  productlevel          SERVERPROPERTY  edition     From  http   support microsoft com kb 321185

User · Answer

SELECT    SERVERNAME AS ServerName  CASE WHEN LEFT CAST serverproperty  productversion   as char   1    9 THEN  2005   WHEN LEFT CAST serverproperty  productversion   as char   2    10 THEN  2008   WHEN LEFT CAST serverproperty  productversion   as char   2    11 THEN  2012  END AS MajorVersion  SERVERPROPERTY   productlevel   AS MinorVersion   SERVERPROPERTY  productversion   AS FullVersion   SERVERPROPERTY   edition   AS Edition

User · Answer

Here s a bit of script I use for testing if a server is 2005 or later  declare  isSqlServer2005 bit select  isSqlServer2005   case when CONVERT int  SUBSTRING CONVERT varchar 15   SERVERPROPERTY  productversion     0  CHARINDEX      CONVERT varchar 15   SERVERPROPERTY  productversion        lt  9 then 0 else 1 end select  isSqlServer2005   Note   updated from original answer  see comment

User · Answer

For SQL Server 2000 and above  I prefer the following parsing of Joe s answer   declare  sqlVers numeric 4 2  select  sqlVers   left cast serverproperty  productversion   as varchar   4    Gives results as follows    Result   Server Version 8 00     SQL 2000 9 00     SQL 2005 10 00    SQL 2008 10 50    SQL 2008R2 11 00    SQL 2012 12 00    SQL 2014   Basic list of version numbers here  or exhaustive list from Microsoft here

User · Answer

If all you want is the major version for T-SQL reasons  the following gives you the year of the SQL Server version for 2000 or later   SELECT left ltrim replace   Version  Microsoft SQL Server       4   This code gracefully handles the extra spaces and tabs for various versions of SQL Server

User · Answer

Try this   if  SELECT LEFT CAST SERVERPROPERTY  productversion   as varchar   2      10  BEGIN

User · Answer

I know this is an older post but I updated the code found in the link  which is dead as of 2013-12-03  mentioned in the answer posted by Matt Rogish   DECLARE  ver nvarchar 128  SET  ver   CAST serverproperty  ProductVersion   AS nvarchar  SET  ver   SUBSTRING  ver  1  CHARINDEX       ver  - 1   IF    ver    7       SELECT  SQL Server 7  ELSE IF    ver    8       SELECT  SQL Server 2000  ELSE IF    ver    9       SELECT  SQL Server 2005  ELSE IF    ver    10       SELECT  SQL Server 2008 2008 R2  ELSE IF    ver    11       SELECT  SQL Server 2012  ELSE IF    ver    12       SELECT  SQL Server 2014  ELSE IF    ver    13       SELECT  SQL Server 2016  ELSE IF    ver    14       SELECT  SQL Server 2017  ELSE    SELECT  Unsupported SQL Server Version

User · Answer

The KB article linked in Joe s post is great for determining which service packs have been installed for any version   Along those same lines  this KB article maps version numbers to specific hotfixes and cumulative updates  but it only applies to SQL05 SP2 and up

User · Answer

select substring   version 0 charindex convert varchar SERVERPROPERTY  productversion       version  len convert varchar SERVERPROPERTY  productversion

User · Answer

Try this   SELECT   VERSION server   SERVERPROPERTY  productversion    SERVERPROPERTY   productlevel    SERVERPROPERTY   edition

User · Answer

Try  SELECT   MICROSOFTVERSION   0x01000000 AS MajorVersionNumber   For more information see  Querying for version edition info

User · Answer

Getting only the major SQL Server version in a single select   SELECT  SUBSTRING ver  1  CHARINDEX      ver  - 1  FROM  SELECT CAST serverproperty  ProductVersion   AS nvarchar  ver  as t   Returns 8 for SQL 2000  9 for SQL 2005 and so on  tested up to 2012

User · Answer

CREATE FUNCTION dbo UFN GET SQL SEVER VERSION      RETURNS sysname AS BEGIN     DECLARE  ServerVersion sysname   ProductVersion sysname   ProductLevel sysname   Edition sysname       SELECT  ProductVersion   CONVERT sysname  SERVERPROPERTY  ProductVersion                  ProductLevel   CONVERT sysname  SERVERPROPERTY  ProductLevel                 Edition   CONVERT sysname  SERVERPROPERTY   Edition         --see  http   support2 microsoft com kb 321185     SELECT  ServerVersion            CASE              WHEN  ProductVersion LIKE  8 00    THEN  Microsoft SQL Server 2000              WHEN  ProductVersion LIKE  9 00    THEN  Microsoft SQL Server 2005              WHEN  ProductVersion LIKE  10 00    THEN  Microsoft SQL Server 2008              WHEN  ProductVersion LIKE  10 50    THEN  Microsoft SQL Server 2008 R2              WHEN  ProductVersion LIKE  11 0   THEN  Microsoft SQL Server 2012              WHEN  ProductVersion LIKE  12 0   THEN  Microsoft SQL Server 2014          END      RETURN  ServerVersion   N      ProductLevel   N         Edition     -      ProductVersion   END GO

[sql-server] How do you check what version of SQL Server for a database using TSQL?

Examples related to sql-server

Examples related to database

Examples related to tsql