代码改变世界

查看SQL Server的版本

  微软一站式示例代码库  阅读(435)  评论(0)  编辑  收藏  举报

文章地址: https://gallery.technet.microsoft.com/Determining-which-version-af0f16f6

微软官方的OneScript Team.提供了一个脚本可以实现如下功能：

1. 查看SQL Server的版本信息

2. 查看SQL Server的补丁信息

3. 查看SQL Server的积累更新(也就是CU)

4. 查看当前SQL Server是否能够升级到2008 or 2012 or 2012，以及具体的版本如标准版、开发版、企业版

并且告知你最新的SP和CU是多少。

 

下面贴上我翻译后的查询结果, 看看此脚本是否符合你的口味

 

代码如下:

注意:为了保证信息的准确性，建议你到官方下载最新的脚本.

下载地址: https://gallery.technet.microsoft.com/Determining-which-version-af0f16f6

如果你有更好的建议,请留言哦.

?
--本脚本不会更新你当前SQL Server中的任何元数据信息,可以放心执行.
--下载地址：https://gallery.technet.microsoft.com/Determining-which-version-af0f16f6
--///////////////////////////////////////////////////////////////////////////////////
DECLARE @ProductVersion     NVARCHAR(20)
DECLARE @ProductLevel       NVARCHAR(20)
DECLARE @UpdateLevel        NVARCHAR(20)
DECLARE @UpdateRef          NVARCHAR(20)
DECLARE @UpdateRefOutput    NVARCHAR(200) = ''
DECLARE @Edition            NVARCHAR(100)
 
DECLARE @ProductName        NVARCHAR(30)
DECLARE @TheLastVersion     NVARCHAR(100)
DECLARE @OtherProduct       NVARCHAR(800)
DECLARE @SPInfo             NVARCHAR(400)
DECLARE @CUInfo             NVARCHAR(400)
 
DECLARE @EditionID          sql_variant
 
DECLARE @ExtendedSupport    NVARCHAR(500)
DECLARE @MainSupportNonUpdate   NVARCHAR(500)
DECLARE @MainSupport        NVARCHAR(500)
 
DECLARE @2016E          NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Enterprise'
DECLARE @2016BI         NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Business Intelligence'
DECLARE @2016Std        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Standard'
DECLARE @2016Web        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Web'
DECLARE @2016Exp        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Express'
DECLARE @2016Dev        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Developer'                                                                
DECLARE @2016Eval       NVARCHAR(50) = + CHAR(13) + 'SQL Server 2016 Evaluation'
 
DECLARE @2014E          NVARCHAR(50) = + CHAR(13) + 'SQL Server 2014 Enterprise'
DECLARE @2014BI         NVARCHAR(50) = + CHAR(13) + 'SQL Server 2014 Business Intelligence'
DECLARE @2014Std        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2014 Standard'
DECLARE @2014Web        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2014 Web'
DECLARE @2014Exp        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2014 Express'
DECLARE @2014Dev        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2014 Developer'                                                                
 
DECLARE @2012E          NVARCHAR(50) = + CHAR(13) + 'SQL Server 2012 Enterprise'
DECLARE @2012BI         NVARCHAR(50) = + CHAR(13) + 'SQL Server 2012 Business Intelligence'
DECLARE @2012Std        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2012 Standard'
DECLARE @2012Web        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2012 Web'
DECLARE @2012Exp        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2012 Express'
DECLARE @2012Dev        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2012 Developer'                                                                    
 
DECLARE @2008R2E        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 R2 Enterprise'
DECLARE @2008R2Dat      NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 R2 Datacenter'
DECLARE @2008R2Std      NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 R2 Standard'
DECLARE @2008R2Wg       NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 R2 Workgroup'
DECLARE @2008R2Dev      NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 R2 Developer'                                                                 
DECLARE @2008R2ExpAdv   NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 R2 Express with Advanced'
 
DECLARE @2008E          NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 Enterprise'
DECLARE @2008Std        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 Standard'
DECLARE @2008Wg         NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 Workgroup'
DECLARE @2008Dev        NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 Developer'
DECLARE @2008ExpAdv     NVARCHAR(50) = + CHAR(13) + 'SQL Server 2008 Express with Advanced'
                                 
SET @ExtendedSupport = '支持的生命周期阶段: 扩展支持. 更多信息与问题：'
                        + CHAR(13) + 'https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL%20Server&Filter=FilterNO'
                        + CHAR(13) + 'https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy'
 
SET @MainSupportNonUpdate    = '支持的生命周期阶段: 主流支持. 更多信息与问题：:'
                        + CHAR(13) + 'https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL%20Server&Filter=FilterNO'
                        + CHAR(13) + 'https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy'
 
SET @MainSupport     = '支持的生命周期阶段: 主流支持. 更多信息与问题：'
                        + CHAR(13) + 'https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL%20Server&Filter=FilterNO'
                        + CHAR(13) + 'https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy'
 
SET @EditionID          = SERVERPROPERTY('EditionID')
SET @ProductVersion     = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductVersion'))
SET @ProductLevel       = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductLevel'))
SET @UpdateLevel    = ISNULL(CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductUpdateLevel')),'')
SET @UpdateRef  = ISNULL(CONVERT(NVARCHAR(20),SERVERPROPERTY('@UpdateRef')),'')
SET @Edition        = CONVERT(NVARCHAR(100),SERVERPROPERTY('Edition'))
 
SELECT  @ProductName =
        CASE SUBSTRING(@ProductVersion,1,4)
            WHEN '12.0' THEN 'SQL Server 2014'
            WHEN '11.0' THEN 'SQL Server 2012'
            WHEN '10.5' THEN 'SQL Server 2008 R2'
            WHEN '10.0' THEN 'SQL Server 2008'
            WHEN '9.00' THEN 'SQL Server 2005'
            WHEN '8.00' THEN 'SQL Server 2000' 
        END,
        @TheLastVersion =
        CASE SUBSTRING(@ProductVersion,1,4)
            WHEN '12.0' THEN 'SQL Server 2014 SP1 CU3'
            WHEN '11.0' THEN 'SQL Server 2012 SP3'
            WHEN '10.5' THEN 'SQL Server 2008 R2 SP3'
            WHEN '10.0' THEN 'SQL Server 2008 SP4'
            WHEN '9.00' THEN 'SQL Server 2005 SP4 CU3'
            WHEN '8.00' THEN 'SQL Server 2000 SP4'
        END
 
DECLARE @Temp1 NVARCHAR(100) = '你已安装最新的补丁包(SP).'
DECLARE @Temp12 NVARCHAR(100) = '你需要安装最新的补丁包(SP):'
SELECT  @SPInfo =
        CASE @ProductName
                WHEN 'SQL Server 2014' THEN
                                            CASE @ProductLevel
                                                WHEN 'SP1' THEN @Temp1 ELSE @Temp12 + 'SP1, <https://support.microsoft.com/en-us/kb/3058865>'
                                            END
                WHEN 'SQL Server 2012' THEN
                                            CASE @ProductLevel
                                                WHEN 'SP3' THEN @Temp1 ELSE @Temp12 + 'SP3, <http://www.microsoft.com/en-us/download/details.aspx?id=50003>'
                                            END
                WHEN 'SQL Server 2008 R2' THEN
                                            CASE @ProductLevel
                                                WHEN 'SP3' THEN @Temp1 ELSE @Temp12 + 'SP3, <https://support.microsoft.com/en-us/kb/2979597>'
                                            END
                WHEN 'SQL Server 2008' THEN
                                            CASE @ProductLevel
                                                WHEN 'SP4' THEN @Temp1 ELSE @Temp12 + 'SP4, <https://support.microsoft.com/en-us/kb/2979596>'
                                            END
                WHEN 'SQL Server 2005' THEN
                                            CASE @ProductLevel
                                                WHEN 'SP4' THEN @Temp1 ELSE @Temp12 + 'SP4, <https://support.microsoft.com/en-us/kb/2463332>'
                                            END
        END,
        @CUInfo =
        CASE @ProductName
                WHEN 'SQL Server 2014' THEN
                                            CASE @ProductVersion
                                                WHEN '12.0.4427.24' THEN '已安装最新的积累更新(CU).'
                                                ELSE '你需要安装SP1的最新积累更新(CU):  CU3, <https://support.microsoft.com/en-us/kb/3094221>'
                                            END
                --WHEN 'SQL Server 2012' THEN
                --                          CASE @ProductVersion
                --                              WHEN '11.0.5634.1' THEN '已安装最新的积累更新(CU).'
                --                              ELSE '你需要安装SP2的最新积累更新(CU):  CU8, <https://support.microsoft.com/en-us/kb/3082561>'
                --                          END
                --WHEN 'SQL Server 2008 R2' THEN
                --WHEN 'SQL Server 2008' THEN
                WHEN 'SQL Server 2005' THEN
                                            CASE @ProductVersion
                                                WHEN '9.00.5266' THEN '已安装最新的积累更新(CU).'
                                                ELSE '你需要安装SP4的最新积累更新(CU):  CU3, <https://support.microsoft.com/en-us/kb/2507769>'
                                            END
        END
 
IF (@UpdateRef <> '')
BEGIN
    SET @UpdateRefOutput = @UpdateRef + ' (' + 'https://support.microsoft.com/kb/'+SUBSTRING(@UpdateRef,3,10)+ ')'
END
 
IF (@ProductName = 'SQL Server 2005')
BEGIN
 
    SELECT  @OtherProduct =
            CASE
                WHEN @ProductLevel IN ('RTM','SP1') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2008E
                            WHEN @EditionID = -1534726760               THEN @2008E+@2008Std
                            WHEN @EditionID = -2117995310               THEN @2008Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2008ExpAdv+@2008Wg
                        END
                WHEN @ProductLevel IN ('SP2','SP3') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2008E + @2008R2E+@2008R2Dat  
                            WHEN @EditionID = -1534726760               THEN @2008E+@2008Std + @2008R2E+@2008R2Std
                            WHEN @EditionID = -2117995310               THEN @2008Dev + @2008R2Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2008ExpAdv+@2008Wg + @2008R2E+@2008R2Std+@2008R2Wg+@2008R2ExpAdv
 
                        END
                WHEN @ProductLevel = 'SP4' THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2008E + @2008R2E+@2008R2Dat + @2012E+@2012BI + @2014E+@2014BI                                                                    
                            WHEN @EditionID = -1534726760               THEN @2008E+@2008Std + @2008R2E+@2008R2Std + @2012E+@2012BI+@2012Std + @2014E+@2014BI+@2014Std
                            WHEN @EditionID = -2117995310               THEN @2008Dev + @2008R2Dev + @2012Dev + @2014Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2008ExpAdv+@2008Wg + @2008R2E+@2008R2Std+@2008R2Wg+@2008R2ExpAdv + @2012E+@2012BI+@2012Std+@2012Web+@2012Exp + @2014E+@2014BI+@2014Std+@2014Web+@2014Exp
 
                        END
            END            
END
 
 
IF (@ProductName = 'SQL Server 2008')
BEGIN
 
    SELECT  @OtherProduct =
            CASE
                WHEN @ProductLevel IN ('RTM','SP1') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2008R2E+@2008R2Dat
                            WHEN @EditionID = -1534726760               THEN @2008R2E+@2008R2Dat+@2008R2Std
                            WHEN @EditionID = -2117995310               THEN @2008R2Dat+@2008R2Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2008R2E+@2008R2Dat+@2008R2Std+@2008R2Wg+@2008R2Dev+@2008R2ExpAdv
                        END
 
                WHEN @ProductLevel = 'SP2' THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2008R2E+@2008R2Dat + @2012E+@2012BI                                                                  
                            WHEN @EditionID = -1534726760               THEN @2008R2E+@2008R2Dat+@2008R2Std + @2012E+@2012BI+@2012Std
                            WHEN @EditionID = -2117995310               THEN @2008R2Dat+@2008R2Dev + @2012Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2008R2E+@2008R2Dat+@2008R2Std+@2008R2Wg+@2008R2Dev+@2008R2ExpAdv
                                                                           + @2012E+@2012BI+@2012Std+@2012Web+@2012Exp
                        END
      
                WHEN @ProductLevel IN('SP3','SP4') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2008R2E+@2008R2Dat + @2012E+@2012BI + @2014E+@2014BI + @2016E+@2016BI                                                                
                            WHEN @EditionID = -1534726760               THEN @2008R2E+@2008R2Dat+@2008R2Std + @2012E+@2012BI+@2012Std
                                                                           + @2014E+@2014BI+@2014Std + @2016E+@2016BI+@2016Std
                            WHEN @EditionID = -2117995310               THEN @2008R2Dat+@2008R2Dev + @2012Dev + @2014Dev + @2016Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2008R2E+@2008R2Dat+@2008R2Std+@2008R2Wg+@2008R2Dev+@2008R2ExpAdv
                                                                           + @2012E+@2012BI+@2012Std+@2012Web+@2012Exp
                                                                           + @2014E+@2014BI+@2014Std+@2014Web+@2014Exp
                        END
            END            
END
 
                                                                           
IF (@ProductName = 'SQL Server 2008 R2')
BEGIN
 
    SELECT  @OtherProduct =
            CASE
                WHEN @ProductLevel = 'RTM' THEN ' 如果你想升级到更高的版本,你需要安装最新的补丁包(SP)'
                WHEN @ProductLevel = 'SP1' THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2012E+@2012BI                                                                        
                            WHEN @EditionID = -1534726760               THEN @2012E+@2012BI+@2012Std
                            WHEN @EditionID = -2117995310               THEN @2012Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2012E+@2012BI+@2012Std+@2012Web+@2012Exp
                        END
                WHEN @ProductLevel IN('SP2','SP3','SP4') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2012E+@2012BI + @2014E+@2014BI + @2016E+@2016BI                                                                          
                            WHEN @EditionID = -1534726760               THEN @2012E+@2012BI+@2012Std + @2014E+@2014BI+@2014Std + @2016E+@2016BI+@2016Std
                            WHEN @EditionID = -2117995310               THEN @2012Dev + @2014Dev + @2016Dev
                            WHEN @Edition LIKE 'Express%'               THEN @2012E+@2012BI+@2012Std+@2012Web+@2012Exp
                                                                           + @2014E+@2014BI+@2014Std+@2014Web+@2014Exp
                                                                           + @2016E+@2016BI+@2016Std+@2016Web+@2016Exp
                        END
            END            
END
 
 
IF (@ProductName = 'SQL Server 2012')
BEGIN
 
    SELECT  @OtherProduct =
            CASE
                WHEN @ProductLevel = 'RTM' THEN ' 如果你想升级到更高的版本,你需要安装最新的补丁包(SP)'
                WHEN @ProductLevel IN('SP1','SP2','SP3','SP4') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2014E+@2014BI + @2016E+@2016BI                                                                               
                            WHEN @EditionID = -1534726760               THEN @2014E+@2014BI+@2014Std + @2016E+@2016BI+@2016Std
                            WHEN @EditionID = -2117995310               THEN @2014Dev + @2016E+@2016BI+@2016Std+@2016Web+@2016Dev
                            WHEN @EditionID = -610778273                THEN @2016E+@2016BI+@2016Std+@2016Web+@2016Dev+@2016Eval
                            WHEN @Edition LIKE 'Express%'               THEN @2014E+@2014BI+@2014Std+@2014Web+@2014Exp
                                                                           + @2016E+@2016BI+@2016Std+@2016Web+@2016Exp+@2016Dev    
                        END
            END            
END
 
IF (@ProductName = 'SQL Server 2014')
BEGIN
 
    SELECT  @OtherProduct =
            CASE
                WHEN @ProductLevel IN ('RTM','SP1') THEN
                        CASE
                            WHEN @EditionID IN (1804890536,1872460670)  THEN @2016E+@2016BI                                                                        
                            WHEN @EditionID = -1534726760               THEN @2016E+@2016BI+@2016Std
                            WHEN @EditionID = -2117995310               THEN @2016E+@2016BI+@2016Std+@2016Web+@2016Dev
                            WHEN @EditionID = -610778273                THEN @2016E+@2016BI+@2016Std+@2016Web+@2016Dev+@2016Eval       
                            WHEN @Edition LIKE 'Express%'               THEN @2016E+@2016BI+@2016Std+@2016Web+@2016Exp+@2016Dev                                                
                        END
            END            
END
 
--显示结果
PRINT REPLICATE('-',105)
PRINT '--//当前的SQL Server版本信息:'
PRINT REPLICATE('-',105)
PRINT '版本号  :          ' + @ProductVersion
PRINT '产品名称:          ' + @ProductName
PRINT '产品级别:          ' + @ProductLevel
PRINT '版本名称:          ' + @Edition
 
IF (@ProductName = 'SQL Server 2014')
    AND ((@ProductLevel = 'RTM' AND SUBSTRING(@UpdateLevel,3,4) >= 10)
          OR (@ProductLevel = 'SP1' AND SUBSTRING(@UpdateLevel,3,4) >= 3)
          OR (@ProductLevel > 'SP1'))
BEGIN
    PRINT '积累更新:     ' + @UpdateLevel
    PRINT '积累更新引用: ' + @UpdateRefOutput
END
ELSE
BEGIN
    IF (@ProductName = 'SQL Server 2014' AND @ProductLevel = 'RTM' AND SUBSTRING(@UpdateLevel,3,4) < 10)
    BEGIN
        PRINT REPLICATE('-',105)
        PRINT '备注:如果你想要查询出 CU .你需要安装相关的补丁包(SP)与积累更新(CU)'
               + CHAR(13) + 'SQL Server 2014 RTM Cumulative Update 10. CU10, <https://support.microsoft.com/en-us/kb/3094220>'   
    END
 
    IF (@ProductName = 'SQL Server 2014' AND @ProductLevel = 'SP1' AND SUBSTRING(@UpdateLevel,3,4) < 3)
    BEGIN
        PRINT REPLICATE('-',105)
        PRINT '备注:如果你想要查询出 CU .你需要安装相关的补丁包(SP)与积累更新(CU)'
               + CHAR(13) + 'SQL Server 2014 SP1 Cumulative Update 3. CU3, <https://support.microsoft.com/en-us/kb/3094221>'
    END
 
    IF (@ProductName <> 'SQL Server 2014')
    BEGIN
        PRINT REPLICATE('-',105)
        PRINT '注意:如果你想要了解 CU 的相关信息,请阅读此文章: https://support.microsoft.com/en-us/kb/321185'
 
    END
 
END
PRINT REPLICATE('-',105)
 
--//支持的生命周期
IF (@ProductName = 'SQL server 2005' OR @ProductName = 'SQL server 2008' OR @ProductName = 'SQL server 2008 R2')
BEGIN
        PRINT @ExtendedSupport
END
 
IF (@ProductName = 'SQL server 2012')
BEGIN
    IF @ProductLevel = 'SP2'
        PRINT @MainSupport
    ELSE
        PRINT @MainSupportNonUpdate
END
 
IF (@ProductName = 'SQL server 2014' OR @ProductName = 'SQL server 2016')
BEGIN
        PRINT @MainSupport
END
 
PRINT REPLICATE('-',105)
PRINT '完整的版本信息:' + CHAR(13) + @@VERSION
 
--//
PRINT REPLICATE('-',105)
PRINT '--//建议你升级到: ' + @TheLastVersion
PRINT REPLICATE('-',105)
PRINT @SPInfo
 
IF @CUInfo IS NOT NULL
PRINT @CUInfo
 
--//
PRINT CHAR(13)
PRINT REPLICATE('-',105)
PRINT '--//当前 SQL Server 可以升级到以下任一产品:'
PRINT REPLICATE('-',105)
PRINT SUBSTRING(@OtherProduct,2,800)
PRINT CHAR(13)
PRINT '如果你想了解更多关于版本升级的相关信息,阅读如下文章:'
      + CHAR(13) +'https://technet.microsoft.com/en-us/library/ms143393(v=sql.120).aspx'
GO

 

编辑推荐：
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
· 使用C#创建一个MCP客户端
官方站点：
点击右上角即可分享
微信分享提示
SQL AI 助手