[Notification] and a version to watch a SPID

WHILE (SELECT    COUNT(*)
FROM     sysprocesses WHERE spid = 230 AND cmd IN ('ALTER table','CREATE index')
)>=1
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA',
    @recipients = 'dba@thebakingdba.com',
    @subject = 'index creation done'

[Jobs] Code to email when job finishes

If you have to run a job outside its normal time, and didn't remember to change the notification before you ran it, fret not. By hitting the system tables, you can have it notify you when the job finishes.

WHILE (SELECT    COUNT(*) 
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= '20090107' and --today's date
         j.NAME = 'your_job_name' AND 
--max step for the job, so you don't get emailed when step 1 of 5 finishes
         h.step_id = 3 
         )=0
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAs',
    @recipients = 'thebakingdba@yourcompanyname.com',
    @query = 'SELECT    j.[name],
         s.step_name,
         h.step_id,
         h.step_name,
         h.run_date,
         h.run_time,
         h.sql_severity,
         h.SERVER,
         h.run_status
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= ''20090107'' and 
         j.NAME = ''your_job_name'' AND 
         h.step_id = 3
' ,
    @subject = 'Your Job Finished',
    @query_result_separator =  ' ',
    @query_result_width = 750,
    @attach_query_result_as_file = 0 ;