Good or bad, but habits

It is very likely that one day or other you'd want to run a sql task at startup of your server. What for? You probably know better than I do but here are a few ideas:

• Create a denormalized table in tempdb
• Check sanity of the server
• Start a monitoring process

Whatever... The following script configures your db server for launching (in each database) the stored procedure called _startup when present:

use master;

if exists 
( 
    select name 
        from master.sys.objects 
        where name = '_startup' 
        and type_desc = 'SQL_STORED_PROCEDURE'
) drop procedure _startup;
go

create procedure _startup
as
    declare @sql varchar(max)
    declare @dbName varchar(50)

    declare curs 
        insensitive cursor for 
            select name 
                from master.sys.databases 
                where database_id > 4
    
    open curs
    fetch curs into @dbName
    
    while(@@fetch_status = 0) begin
        select @sql = 'use ['+ @dbName + ']'
        exec(@sql)
        select @sql = '
        if exists(
            select * 
                from [' + @dbName + '].sys.objects 
                where type_desc = ''SQL_STORED_PROCEDURE'' 
                and name = ''_startup''
            ) exec(''[' + @dbName + '].dbo._startup'')'
        exec (@sql)
        fetch curs into @dbName
    end
    
    close curs
    deallocate curs
go

exec sp_procoption '_startup', 'startup', 1