SQL – Check If A Table Exists

Check if a table exists in a database:

IF OBJECT_ID(‘database.dbo.table’) > 0
BEGIN
   //Code to run if table exists. e.g. delete table.
   //drop table database.dbo.table
END

 

 

Advertisements

SQL – How to get the status of an SQL job (sp_help_job)

 

Here is a simple script using sp_help_job to display information about jobs that are used by SQL Server Agent:

USE msdb
EXEC dbo.sp_help_job @JOB_NAME = ‘JOB_NAME’, @job_aspect=‘JOB’

Look for ‘current_execution_status’ to get the status of the job.

1 = Executing.
2 = Waiting for thread.
3 = Between retries.
4 = Idle.
5 = Suspended.
7 = Performing completion actions.

 

Here is a more detailed script to store the results of sp_help_job into a table and query the results:

DECLARE @JOBNAME VARCHAR(100)
SET @JOBNAME = ‘JOB_NAME’

CREATE TABLE #JOBSTATUS(
       job_id uniqueidentifier 
      ,originating_server nvarchar(30)
      ,name sysname
      ,enabled tinyint
      ,description nvarchar(512)
     
,start_step_id int
     
,category sysname
     
,owner sysname
      ,notify_level_eventlog int
     
,notify_level_email int
     
,notify_level_netsend int
     
,notify_level_page int
     
,notify_email_operator sysname
     
,notify_netsend_operator sysname
     
,notify_page_operator sysname
     
,delete_level int
     
,date_created datetime
     
,date_modified datetime
     
,version_number int
     
,last_run_date int
     
,last_run_time int
     
,last_run_outcome int
     
,next_run_date int
     
,next_run_time int
     
,next_run_schedule_id int
     
,current_execution_status int
     
,current_execution_step sysname 
     
,current_retry_attempt int
     
,has_step int
     
,has_schedule int
     
,has_target int
     
,type int     
); 

INSERT INTO #JOBSTATUS
EXEC dbo.sp_help_job
 @JOB_NAME = @JOBNAME
,@job_aspect=‘JOB’

SELECT CASE current_execution_status
WHEN ‘0’ THEN ‘Returns only those jobs that are not idle or suspended. ‘        
WHEN ‘1’ THEN ‘Executing.’
        
WHEN ‘2’ THEN ‘Waiting for thread.’
       
WHEN ‘3’ THEN ‘Between retries.’
        
WHEN ‘4’ THEN ‘Idle.’

WHEN ‘5’ THEN ‘Suspended.’

WHEN ‘6’ THEN

WHEN ‘7’ THEN ‘Performing completion actions.’

ELSE ‘UNKNOWN’
END as ‘current_execution_status’
FROM #JOBSTATUS

DROP TABLE #JOBSTATUS

 

OUTPUT:


current_execution_status
——————————————————–
Idle.

T-SQL – Using CASE In An Update Statement

The following example will update [TABLE] and assign a value to the PRICEBRACKET field based on the value of the pice field:

UPDATE [TABLE]
SET PRICEBRACKET =
CASE
WHEN (price < 5000)
THEN ‘Less Than 5K’
WHEN (price >= 5000 AND price <10000)
THEN ‘Between 5K and 10K’
WHEN (price >= 10000 AND price <20000)
THEN ‘Between 10K and 20K’
WHEN (price >= 20000 AND price <50000)
THEN ‘Between 20K and 50K’
ELSE ‘Above 50K’
END