sql-server – 我的SQL Server是否已修补？

我有第二个视图,是嵌套视图 – 坏我,dbo.MyPatchLevel,它使用上面的版本数据与我当前的实例进行比较并生成输出,让我知道它有多糟糕.

CREATE VIEW dbo.MyPatchLevel
AS
WITH MostRecentBuild AS
(
    SELECT
        SRC.Build,SRC.SimpleVersion,SRC.[KB / Description]
    FROM
        dbo.PatchLevel AS SRC
    WHERE
        SRC.[Release Date] =
    (
        SELECT
            MAX(SRCI.[Release Date])
        FROM
            dbo.PatchLevel AS SRCI
        WHERE
            SRCI.SimpleVersion = SRC.SimpleVersion
    )
    AND
        SRC.Build =
    (
        SELECT
            MAX(SRCI.Build)
        FROM
            dbo.PatchLevel AS SRCI
        WHERE
            SRCI.SimpleVersion = SRC.SimpleVersion
            AND SRCI.[Release Date] = SRC.[Release Date]
    )

),MyVersion AS
(
    SELECT
        SRC.Build,MRB.[KB / Description],D.ProductVersion,D.ProductLevel,D.Edition,D.Version,MRB.Build AS MostRecentBuild,MRB.[Release Date] AS MostRecentReleaseDate
    FROM
        dbo.PatchLevel AS SRC
        INNER JOIN
        (
            SELECT 
                SERVERPROPERTY('productversion') AS ProductVersion,SERVERPROPERTY ('edition') AS Edition,@@VERSION AS Version
        )D 
        ON D.ProductVersion  = SRC.Build
        INNER JOIN
            MostRecentBuild AS MRB
            ON MRB.SimpleVersion = SRC.SimpleVersion
)
SELECT
    MV.ProductVersion AS CurrentVersion,MV.MostRecentBuild,MV.[Release Date],MV.MostRecentReleaseDate,DATEDIFF(DAY,MV.MostRecentReleaseDate) AS DaysSincePatchAvailable,MV.[File version],MV.ProductLevel,MV.[KB / Description] AS [Most Recent KB / Description],MV.Edition,MV.Version,MV.SimpleVersion,CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS sysname) AS Server,CAST(SERVERPROPERTY('InstanceName') AS sysname) AS Instance,CAST(SERVERPROPERTY('ServerName') AS sysname)  AS ServerName
FROM
    MyVersion AS MV;
GO

我会使用像这样的查询

SELECT
    MPL.CurrentVersion,MPL.MostRecentBuild,MPL.[Release Date],MPL.MostRecentReleaseDate,MPL.DaysSincePatchAvailable,MPL.[Most Recent KB / Description]
FROM
    dbo.MyPatchLevel AS MPL;

结果

CurrentVersion  MostRecentBuild  Release Date  MostRecentReleaseDate  DaysSincePatchAvailable  Most Recent KB / Description
12.0.4416.0     12.0.4422.0      2015-06-22    2015-08-17             56                       3075950 Cumulative update package 2 (CU2) for SQL Server 2014 Service Pack 1

有了这些查询结果,我知道我是否已修补到最新版本,自修补以来已经修补了多长时间以及在当前补丁状态下读取KB的内容.

脚本也在github托管

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!