Maintenance Plan MetaData


I was recently asked about which tables are involved in a maintenance plan in SQL Server 2005.The primary tables are:


msdb.dbo.sysmaintplan_subplans
msdb.dbo.sysmaintplan_log
msdb.dbo.sysmaintplan_logdetail
msdb.dbo.sysdbmaintplans
msdb.dbo.sysdbmaintplan_jobs
msdb.dbo.sysdbmaintplan_databases
msdb.dbo.sysdbmaintplan_history
msdb.dbo.sysmaintplan_plans


Also check the column table_name where the value is ‘%maint%’ in information_schema.tables.