System View for Maintenance Plan Steps?

  • Where are maintenance plan steps stored? Is there a system view or system table for them? I have checked sysmaintplan_plans and sysdtspackages90 but these just contain one row per plan, not one row per step.

  • I know in SQL 2000 there are some tables

    In 2005/2008 I am not certain

    but Maint. Plans will be stored in MSDB, if you log in using Integration Service you'll see the packages

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • I can open the packages no problem but I was looking for which system views store the information of each step that they contain. I can't find anything.

  • Have you checked [sysmaintplan_subplans]

    MJ

  • Have you checked [sysmaintplan_subplans]

    MJ

  • It's still 1 row per SUBPLAN, and 1 row per PLAN

    SELECT TOP 500 *

    FROM [msdb].[dbo].[sysmaintplan_subplans] SS

    inner join msdb.dbo.sysmaintplan_plans SP ON SS.plan_id = SP.id

    Didn't find too many tables/views with plan_id column to be useful either

    select * from INFORMATION_SCHEMA.COLUMNS

    where COLUMN_NAME = 'plan_id'

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply