SQL 2012, get databases associated with a maintenance plan.

I have searched for hours. I am looking for  some TSQL that will show me the following columns -

Job Name : Maintenance Plan Name: Database Name

I need one record for each database name in the maintenance plan.

Sounds simple, but I can figure it out, nor found an example.

Thanks!!!
Ken
yankeekAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't find nothing because maintenance plans are stored as SSIS package and not tables.
The maximum that you can achieve is to have some information about maintenance plans:
USE msdb
GO

select *
from sysmaintplan_plans p
	inner join sysmaintplan_subplans sp on p.id = sp.plan_id

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
By the way, you can query for stored SSIS packages and if you know XML you may work with the XML information. Just run the following query and click on the link result for your maintenance plan record:
SELECT name, CAST(CAST(packagedata as varbinary(max)) as xml) AS PackageSource
      ,packagetype, id, description,createdate 
FROM msdb.dbo.sysssispackages

Open in new window

Mark WillsTopic AdvisorCommented:
Have you played with : sp_help_maintenance_plan will take a bit of fiddling but does contain the items in question...

Also worth remembering that some of the older maint plans tables are still supported but as views. Which means code like :
SELECT plan_name, database_name  
FROM msdb.dbo.sysdbmaintplans 
INNER JOIN msdb.dbo.sysdbmaintplan_databases 
ON msdb.dbo.sysdbmaintplans.plan_id = msdb.dbo.sysdbmaintplan_databases.plan_id

Open in new window

Still works (and might inspire you). Albeit for the older style maint plans...
For SSIS generated plans you need something more like :
SELECT *
FROM msdb.dbo.sysmaintplan_plans AS s 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id 

Open in new window


Will see if there is something more lying around...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
I was playing around with XML and got this query:
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
	, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
	,ssis AS (SELECT name, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
		FROM [msdb].[dbo].[sysssispackages]
		WHERE name = 'MaintenancePlan')

SELECT DISTINCT s.name
	, o.value('(SQLTask:SqlTaskData/@SQLTask:TaskName)[1]', 'NVARCHAR(MAX)') AS Task
	, c.value('(SQLTask:SelectedDatabases/@SQLTask:DatabaseName)[1]', 'NVARCHAR(MAX)') AS DatabaseName
FROM ssis s
    CROSS APPLY package.nodes('//DTS:ObjectData') t(o)
    CROSS APPLY package.nodes('//SQLTask:SqlTaskData') d(c)
WHERE o.exist('SQLTask:SqlTaskData/@SQLTask:TaskName') = 1
    AND c.exist('SQLTask:SelectedDatabases/@SQLTask:DatabaseName') = 1

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to replace the line:
WHERE name = 'MaintenancePlan'
with your Maintenance Plan name.
Mark WillsTopic AdvisorCommented:
*ROTFLMAO*

Just came up with :
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTSJob
                   ,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
,SSISJob AS 
   (SELECT name, description, CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS Plans
    FROM   msdb.dbo.sysssispackages)

SELECT SSISJob.name AS Plan_Name
      ,SSISJob.Description AS Plan_Description
      ,Jobs.value  ('(SQLTask:SqlTaskData/@SQLTask:TaskName)[1]', 'NVARCHAR(MAX)') AS JOB_name
	  ,DBname.value('(@SQLTask:DatabaseName)[1]','nvarchar(max)') AS DBname 
FROM  SSISJob 
CROSS APPLY Plans.nodes('//DTSJob:ObjectData') Obj(Jobs)
CROSS APPLY Obj.Jobs.nodes('SQLTask:SqlTaskData/SQLTask:SelectedDatabases') DBS(DBname)
WHERE Jobs.exist('SQLTask:SqlTaskData') = 1 

Open in new window


Inspired by Chad's post - now lives in : https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/01/21/querying-maintenance-plans

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yankeekAuthor Commented:
PERFECT!!!!!

That is what I was looking for!!

Thank you very much

Ken
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.