如何使用TSQL检查数据库中的SQL Server版本?

Is there a system stored procedure to get the version #?

是否有系统存储过程来获得版本号?

16 个解决方案

#1

Try

试一试

SELECT @@VERSION 

or for SQL Server 2000 and above the following is easier to parse :)

或者对于SQL Server 2000，下面的代码更容易解析:)

SELECT SERVERPROPERTY('productversion')
     , SERVERPROPERTY('productlevel')
     , SERVERPROPERTY('edition')

From: http://support.microsoft.com/kb/321185

来自:http://support.microsoft.com/kb/321185

#2

SELECT @@VERSION

选择@@VERSION

#3

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:

我知道这是一个较老的帖子，但我更新了在链接中发现的代码(截至2013-12-03年)。

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
   SELECT 'Unsupported SQL Server Version'

#4

For SQL Server 2000 and above, I prefer the following parsing of Joe's answer:

对于SQL Server 2000和以上，我更喜欢以下解析Joe的答案:

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.

这里是版本号的基本列表，或者是微软的详尽列表。

#5

There is another extended Stored Procedure which can be used to see the Version info:

还有一个扩展存储过程，可以用来查看版本信息:

exec [master].sys.[xp_msver]

#6

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

#7

Here's a bit of script I use for testing if a server is 2005 or later

这里有一个脚本，我用于测试服务器是在2005年还是以后。

declare @isSqlServer2005 bit
select @isSqlServer2005 = case when CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion'))))) < 9 then 0 else 1 end
select @isSqlServer2005

Note : updated from original answer (see comment)

注意:从原始答案更新(参见注释)

#8

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.

在Joe的post中链接的KB文章很好地决定了已经为任何版本安装了哪些服务包。沿着这些相同的行，这个KB的文章将版本号映射到特定的热修复和累积更新，但是它只适用于SQL05 SP2和up。

#9

Try this:

试试这个:

if (SELECT LEFT(CAST(SERVERPROPERTY('productversion') as varchar), 2)) = '10'
BEGIN

#10

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

#11

Getting only the major SQL Server version in a single select:

仅在一个select中获取主要的SQL Server版本:

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).

返回SQL 2000的8,SQL 2005的9，等等(测试到2012年)。

#12

Try

试一试

SELECT @@MICROSOFTVERSION / 0x01000000 AS MajorVersionNumber

For more information see: Querying for version/edition info

有关更多信息，请参见:查询版本/版本信息。

#13

select substring(@@version,0,charindex(convert(varchar,SERVERPROPERTY('productversion')) ,@@version)+len(convert(varchar,SERVERPROPERTY('productversion')))) 

#14

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.

如果您想要的是主要版本的T-SQL原因，下面给出了2000年或以后的SQL Server版本的年份。

SELECT left(ltrim(replace(@@Version,'Microsoft SQL Server','')),4)

选择离开(ltrim(替换(@@Version,Microsoft SQL Server,”)),4)

This code gracefully handles the extra spaces and tabs for various versions of SQL Server.

此代码优雅地处理SQL Server的各种版本的额外空间和选项卡。

#15

Try this:

试试这个:

SELECT @@VERSION[server], SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

#16

select 'the sqlserver is '+substring(@@VERSION,21,5) 'sql version'

选择“sqlserver is”+子字符串(@@VERSION,21,5)“sql版本”