如何在 SQL Server 中搜索执行特定任务的维护计划？

是的。这可以通过使用系统 SSIS 表和 XQuery 的组合来完成。

注意：在 SQL 2012 中，这仅适用于在 SSMS 中创建的维护计划。如果您使用 Data Tools 部署了通过项目部署模型执行维护的包，这将不起作用。

• SQL Server 将维护计划保存为 SSIS 包，因此需要系统 SSIS 表（sysssispackages 和 sysssispackagefolders）。
• 我们将在其中找到对您正在搜索的任务的引用的包级数据将在列 sysssispackages.packagedata 中找到。

XQuery

如果您不熟悉 XQuery，可以从这里开始。

查找任务的任务 ExecutionType(s)

您需要找到的第一件事是您要查找的任务的 ExecutionType。

1. 查找包含相关任务的现有维护计划或制定新的维护计划。
2. 记住您要查找的维护计划的名称。

3. 查询 msdb..sysssispackages 以获取计划名称。

   根据维护计划名称检索包 XML 的示例查询：

    SELECT 
       name PlanName   
       ,createdate PlanCreated
       ,CAST(CAST(pack.[packagedata] AS varbinary(max)) AS XML) PackageDataXml 
    FROM msdb.dbo.sysssispackages pack
    WHERE name = 'RebuildIndexes'
   

4. 单击 PackageDataXml 列中的结果，这将在另一个查询选项卡中打开原始 XML。

5. 在新打开的 XML 选项卡中，搜索字符串“ExecutableType”。（这里不需要什么花哨的东西 - 只是很好的老式 CTRL+F。）

   • 我们这样做是因为每个任务都保存为一个 DTS:Executable 节点，并且每个 DTS:Executable 节点都有一个 DTS:ExecutableType。Executable 类型是我们可以在最终查询中用于搜索特定任务的类型。
   • 您会发现一些与我们的任务无关的可执行节点的 ExecutableTypes。继续搜索，直到找到相关的 Executable 节点。
   • 当您看到它时，您会知道它是一个很好的名称，因为 DTS:ExecutableType 的值将具有一个有用的描述性名称。
   • 只抓取 ExecutableType 的第一部分，通常是逗号之前的字符串部分。

使用 ExecutableType(s) 搜索维护计划

以下查询使用在上述步骤中找到的 XQuery 和 ExecutableType 值在所有维护计划中进行搜索。

• 注释掉的行允许过滤与活动作业和/或活动日程相关的计划。
• 感谢StuartLC ，他帮助我完成了其中的 XQuery 部分。
• 仅在 2012 和 2008R2 上进行了测试。

/*
  Description: Search SSISPackages w/ xQuery for specific 
  Maintenance Plan tasks. In this example: 

    --> Index Rebuild
    --> Shrink Database
    --> Update Statistics

Known compatible versions: 2008R2 & 2012

    --> No 2008 or 2005 instances to try it out on. :'(
*/

-- Build a temp table to retrieve the list of plans
-- that use the tasks we're searching for.

IF OBJECT_ID('tempdb..#BadPlans') IS NOT NULL 
  DROP TABLE #BadPlans;

CREATE TABLE #BadPlans 
(
  PlanName nvarchar(128)
  ,PlanCreated datetime
  ,PlanFolder nvarchar(128)
  ,TaskDescription varchar(50)
  ,TaskExecutableType varchar(200)
);

;WITH XMLNAMESPACES 
(
  'www.microsoft.com/SqlServer/Dts' AS DTS
  ,'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
INSERT INTO #BadPlans

--******2012 Results******

SELECT 
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(@DTS:Description)[1]', 'varchar(50)') 
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)') 
    AS TaskExecutableType
FROM 
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid 
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id            
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id        
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id    
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY 
-- Version-specific XPath for 2012. Look at this singular-plural-singular-plural-singular madness.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable/DTS:Executables/DTS:Executable') 
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%'

UNION ALL

--******2008R2 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(DTS:Property[@DTS:Name="ObjectName"])[1]', 'varchar(50)') 
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)') 
    AS TaskExecutableType
FROM 
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid 
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id            
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id        
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id    
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY 
-- Version-specific XPath for 2008R2. Not as much madness here.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executable/DTS:Executable') 
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%';

SELECT * FROM #BadPlans;