'Update one step of every SQL Server job

I have a server that has around 90 databases. On this server I have one job per database that cleans some table logs. All jobs are named as DBName_CleanLogs_job. Each job has 5 steps, that are all pretty similar, some of them are identical.

What I need to do is to update all jobs with a script, to override a given step in each job, for example, the step named "delete logs".

I managed to get all the databases names, but I'm not able to include them in the query to CONCAT the DBName with the job name.

Here is an example of what I tried to do.

USE master
GO
Declare @variable table (name nvarchar(128));
DECLARE @cnt INT = 0;
declare @name nvarchar(128);

INSERT INTO @variable (name)
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

SELECT * from @variable --check the output

BEGIN
  
SELECT name = @name
FROM @variable 

  
EXEC msdb.dbo.sp_update_jobstep 
    @job_name = SELECT  CONCAT (@name , '_CleanLogs_job'),  
    @step_id = 1, 
    @step_name = N'Delete Table Logs',  
    @subsystem = N'TSQL',  
    @command = N'My SQL Command';
END

The error message in this block of code is that the SP is expecting a stepId which was not given.

Thank you in advance for your help!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source