How to check SQL Server version

Question

What are the possible ways to determine the deployed SQL Server version   I   ve tried to do it using the SQL Server software  I want to do it using a command line SQL statement

User · Answer

Here is what i have done to find the version  just write SELECT   version and it will give you the version

User · Answer

TL DR  SQLCMD -S  LOCAL  -E -V 16 -Q  IF ISNULL CAST SERVERPROPERTY  ProductMajorVersion   AS INT  0  lt 11  RAISERROR  You need SQL 2012 or later   16 1   IF ERRORLEVEL 1 GOTO  ExitFail     This uses SQLCMD  comes with SQL Server  to connect to the local server instance using Windows auth  throw an error if a version check fails and return the   ERROR as the command line ERRORLEVEL if    16  and the second line goes to the  ExitFail label if the aforementioned ERRORLEVEL is    1    Watchas  Gotchas  amp  More Info  For SQL 2000  you can use the SERVERPROPERTY to determine a lot of this info   While SQL 2008  supports the ProductMajorVersion  amp  ProductMinorVersion properties  ProductVersion has been around since 2000  remembering that if a property is not supported the function returns NULL    If you are interested in earlier versions you can use the PARSENAME function to split the ProductVersion  remembering the  parts  are numbered right to left i e  PARSENAME  a b c   1  returns c    Also remember that PARSENAME  a b c   4  returns NULL  because SQL 2005 and earlier only used 3 parts in the version number   So for SQL 2008  you can simply use    SELECT     SERVERPROPERTY  ProductVersion   AS ProductVersion      CAST SERVERPROPERTY  ProductMajorVersion    AS INT  AS ProductMajorVersion      CAST SERVERPROPERTY   ProductMinorVersion   AS INT  AS ProductMinorVersion      For SQL 2000-2005 you can use    SELECT     SERVERPROPERTY  ProductVersion   AS ProductVersion      CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 3 ELSE 4 END  AS INT  AS ProductVersion Major      CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 2 ELSE 3 END  AS INT  AS ProductVersion Minor      CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 1 ELSE 2 END  AS INT  AS ProductVersion Revision      CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 0 ELSE 1 END  AS INT  AS ProductVersion Build      the PARSENAME     0  is a hack to improve readability   So a check for a SQL 2000  version would be   IF  CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 3 ELSE 4 END  AS INT   lt  10  -- SQL2008 OR        CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 3 ELSE 4 END  AS INT    10  -- SQL2008 AND  CAST PARSENAME CAST SERVERPROPERTY  ProductVersion   AS SYSNAME   CASE WHEN SERVERPROPERTY  ProductVersion   IS NULL THEN 2 ELSE 1 END  AS INT   lt  5   -- R2  this may need to be 50           RAISERROR  You need SQL 2008R2 or later    16  1       This is a lot simpler if you re only only interested in SQL 2008  because SERVERPROPERTY  ProductMajorVersion   returns NULL for earlier versions  so you can use   IF  ISNULL CAST SERVERPROPERTY  ProductMajorVersion   AS INT   0   lt  11  -- SQL2012     RAISERROR  You need SQL 2012 or later    16  1       And you can use the ProductLevel and Edition  or EngineEdition  properties to determine RTM   SPn   CTPn and Dev   Std   Ent   etc respectively   SELECT     CAST SERVERPROPERTY  ProductVersion   AS SYSNAME  AS ProductVersion      CAST SERVERPROPERTY  ProductLevel   AS SYSNAME    AS ProductLevel      CAST SERVERPROPERTY  Edition   AS SYSNAME         AS Edition      CAST SERVERPROPERTY  EngineEdition   AS INT       AS EngineEdition      FYI the major SQL version numbers are    8   SQL 2000 9   SQL 2005 10   SQL 2008  and 10 5   SQL 2008R2  11   SQL 2012 12   SQL 2014 13   SQL 2016 14   SQL 2017   And this all works for SQL Azure too   EDITED  You may also want to check your DB compatibility level since it could be set to a lower compatibility   IF EXISTS  SELECT   FROM sys databases WHERE database id DB ID   AND  compatibility level   lt  110      RAISERROR  Database compatibility level must be SQL2008R2 or later  110     16  1

User · Answer

select charindex    Express    version    if this value is 0  is not a express edition

User · Answer

Simply use  SELECT   VERSION   Sample output  Microsoft SQL Server 2012 - 11 0 2100 60  X64   Feb 10 2012 19 39 15  Copyright  c  Microsoft Corporation Express Edition  64-bit  on Windows NT 6 2  lt X64 gt   Build 9200      Source  How to check sql server version   Various ways explained

User · Answer

declare  sqlVers numeric 4 2  select  sqlVers   left cast serverproperty  productversion   as varchar   4    Gives 8 00  9 00  10 00 and 10 50 for SQL 2000  2005  2008 and 2008R2 respectively   Also  Try the system extended procedure xp msver  You can call this stored procedure like  exec master  xp msver

User · Answer

Following are possible ways to see the version   Method 1  Connect to the instance of SQL Server  and then run the following query   Select   version   An example of the output of this query is as follows   Microsoft SQL Server 2008  SP1  - 10 0 2531 0  X64    Mar 29 2009  10 11 52   Copyright  c  1988-2008 Microsoft Corporation  Express  Edition  64-bit  on Windows NT 6 1  lt X64 gt   Build 7600      Method 2  Connect to the server by using Object Explorer in SQL Server Management Studio  After Object Explorer is connected  it will show the version information in parentheses  together with the user name that is used to connect to the specific instance of SQL Server   Method 3  Look at the first few lines of the Errorlog file for that instance  By default  the error log is located at Program Files Microsoft SQL Server MSSQL n MSSQL LOG ERRORLOG and ERRORLOG n files  The entries may resemble the following   2011-03-27 22 31 33 50 Server      Microsoft SQL Server 2008  SP1  - 10 0 2531 0  X64                  Mar 29 2009 10 11 52                 Copyright  c  1988-2008 Microsoft Corporation                Express Edition  64-bit  on Windows NT 6 1  lt X64 gt   Build 7600      As you can see  this entry gives all the necessary information about the product  such as version  product level  64-bit versus 32-bit  the edition of SQL Server  and the OS version on which SQL Server is running   Method 4  Connect to the instance of SQL Server  and then run the following query   SELECT SERVERPROPERTY  productversion    SERVERPROPERTY   productlevel    SERVERPROPERTY   edition     Note This query works with any instance of SQL Server 2000 or of a later version

[sql-server-2008] How to check SQL Server version

Examples related to sql-server-2008

Examples related to version