IF OBJECT_ID('tempdb..#SQL') IS NOT NULL
DROP TABLE #SQL
SELECT DISTINCT
j.name
INTO #SQL
FROM msdb.dbo.sysjobsteps st (NOLOCK)
INNER JOIN msdb.dbo.sysjobs AS j (NOLOCK) ON j.job_id = st.job_id
CROSS APPLY (SELECT CAST(SUM(Counter) AS VARCHAR) + '-' + Util.dbo.StringConcat(subsystem + ' ('
+ CAST(counter AS VARCHAR) + ')',
', ') JobSubsystemList
FROM (SELECT s2.subsystem,
COUNT(*) AS Counter
FROM msdb.dbo.sysjobsteps s2 (NOLOCK)
WHERE s2.Job_id = st.job_id
GROUP BY s2.subsystem) b) k
WHERE subsystem = 'TSQL'
OR (subsystem = 'SSIS'
AND command LIKE '%Maintenance Plans\%')
IF OBJECT_ID('tempdb..#APPLICATION') IS NOT NULL
DROP TABLE #SSIS
SELECT DISTINCT
j.name
INTO #APPLICATION
FROM msdb.dbo.sysjobsteps st (NOLOCK)
INNER JOIN msdb.dbo.sysjobs AS j (NOLOCK) ON j.job_id = st.job_id
CROSS APPLY (SELECT CAST(SUM(Counter) AS VARCHAR) + '-' + Util.dbo.StringConcat(subsystem + ' ('
+ CAST(counter AS VARCHAR) + ')',
', ') JobSubsystemList
FROM (SELECT s2.subsystem,
COUNT(*) AS Counter
FROM msdb.dbo.sysjobsteps s2 (NOLOCK)
WHERE s2.Job_id = st.job_id
GROUP BY s2.subsystem) b) k
WHERE J.name NOT IN ('syspolicy_purge_history')
AND subsystem IN ('CmdExec')
OR (subsystem = 'SSIS'
AND command NOT LIKE '%Maintenance Plans\%')
GO
DECLARE @DeleteFromSQL VARCHAR(MAX),
@DeleteFromApp VARCHAR(MAX)
SELECT @DeleteFromApp = Util.dbo.StringConcat(CASE WHEN IsSQL = 1
THEN 'EXEC msdb.dbo.sp_delete_job @job_name = '''
+ REPLACE(name, '''', '''''') + ''''
END, '
GO
'),
@DeleteFromSQL = Util.dbo.StringConcat(CASE WHEN IsSQL = 0
THEN 'EXEC msdb.dbo.sp_delete_job @job_name = '''
+ REPLACE(name, '''', '''''') + ''''
END, '
GO
')
FROM msdb.dbo.sysjobs a
CROSS APPLY (SELECT CASE WHEN EXISTS ( SELECT *
FROM #APPLICATION b
WHERE a.NAME = b.name ) THEN 0
WHEN EXISTS ( SELECT *
FROM #sql b
WHERE a.NAME = b.name ) THEN 1
ELSE 0
END AS IsSQL) b
PRINT '--- DELETE FROM SQL SERVER !!!'
EXEC Util.dbo.PrintLargeText
@DeleteFromSQL
PRINT '--- DELETE FROM APPLICATION SERVER !!!'
EXEC Util.dbo.PrintLargeText
@DeleteFromApp
---- DELETE FROM SQL SERVER
--SELECT 'EXEC msdb.dbo.sp_delete_job @job_name = ''' + REPLACE(name, '''', '''''') + '''
--GO'
--FROM msdb.dbo.sysjobs a
--WHERE NOT EXISTS ( SELECT *
-- FROM #SQL t
-- WHERE t.name = a.name )
---- DELETE FROM APP SERVER
--SELECT 'EXEC msdb.dbo.sp_delete_job @job_name = ''' + REPLACE(name, '''', '''''') + '''
--GO'
--FROM #SQL