Properly execute the calling of SQL Server Agent job on each customer database by using cursors in SQL Server

Question

Need to update the SQL Server Agent job (xyz) to remove usage of stored procedure (sp_abc) and properly execute the calling of SQL Server Agent job (xyz) on each customer database by using cursors in SQL Server.

Need help, how to write down the code

Answer 1

How you can see the list of all user database :

SELECT *, name, database_id, create_date  
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

and how to use the cursor to seek all user database :

DECLARE @DatabaseName VARCHAR(MAX)

DECLARE DatabaseList CURSOR FOR 
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
OPEN DatabaseList;
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @DatabaseName
        --Exec your SP
    FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    END;
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

Finally, if you have stored procedure(sp_abc) for every single database this is the answer :

DECLARE @DatabaseName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

DECLARE DatabaseList CURSOR FOR 
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
OPEN DatabaseList;
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @DatabaseName
        --Exec your SP
        Set @SQL = CONCAT(@DatabaseName, '.dbo.', 'sp_help')
        PRINT @SQL
        EXECUTE sp_executesql @SQL

    FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    END;
CLOSE DatabaseList;
DEALLOCATE DatabaseList;