This T-SQL code uses sp_help_job to check to current status of a specific job (JobID) and waits until its Idle.
About sp_help_job:
Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server. More info?
First step is to create a simple SQL Agent Job called ‘WaitJob‘ that executes the statement:
WAITFOR DELAY '00:00:05'
Note: Be sure that ‘Ad Hoc Distributed Queries’ are enabled (More info?).
The T-SQL code checks the current status of a specific job (in this case ‘WaitJob’ and waits for it (by using the WAITFOR-statement):
The results:
Download the source code here.