SELECT  *
FROM
(
    SELECT  name,
        [Status] = CASE WHEN (@@OPTIONS & number) = 0 THEN 'OFF' ELSE 'ON' END
    FROM    master.dbo.spt_values
    WHERE       type='SOP'
        AND number > 0
) AS X

--------------------------------------------------
-- server is SQLBOCSSLON1

-- this script creates an AD HOC job each step is a missing index in improving performance of
--'spCustomerSearchBySurnameRaw'
--'spCustomerSearchByFirstNameAndSurnameRaw'

-- this file will be located at:
--B:Projects2015SQLBOCSSLON1SP TuningTESTspCustomerSearchBySurnameRaw20150529

--Marcelo Miorelli
--29-May-2015
--------------------------------------------------

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Ad Hoc - Create new Indexes - Bocss2',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]
ON [dbo].[tblBAddress]
([blnIsCurrent], [strDeliveryPointSuffix], [strCountryCode], [strPostalCodeLookup])
INCLUDE (lngAddressID) WITH (ONLINE = ON)',
        @database_name=N'Bocss2',
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]
ON [dbo].[tblBAddress] ([blnIsCurrent])
INCLUDE (lngAddressID,strAccountCode) WITH (ONLINE = ON)',
        @database_name=N'Bocss2',
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED [INDEX IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]',
        @step_id=3,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]
ON [dbo].[tblBColdList]
([sintMarketID], [strAddressLine3], [sdtmCopiedToAccount], [blnUsable], [strSurname], [strPostalCodeLookup])
INCLUDE (strAccountCode,strTitle,strFirstNames) WITH (ONLINE = ON)

',
        @database_name=N'Bocss2',
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]',
        @step_id=4,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]
ON [dbo].[tblBColdList] ([strFirstNames], [blnUsable], [strSurname]) INCLUDE (strAccountCode,sintMarketID,strTitle,strAddressLine1,strAddressLine2,strAddressLine3,strAddressLine4,strAddressLine5,strAddressLine6,strPostalCode,strCountryCode,strEmail) WITH (ONLINE = ON)
',
        @database_name=N'Bocss2',
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED [INDEX IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]',
        @step_id=5,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]
ON [dbo].[tblBColdList] ([strFirstNames], [blnUsable]) INCLUDE (strAccountCode,sintMarketID,strTitle,strSurname,strAddressLine1,strAddressLine2,strAddressLine3,strAddressLine4,strAddressLine5,strAddressLine6,strPostalCode,strCountryCode,strEmail) WITH (ONLINE = ON)
',
        @database_name=N'Bocss2',
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]',
        @step_id=6,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]
ON [dbo].[tblBColdList] ([strFirstNames], [strPostalCodeLookup]) INCLUDE (strAccountCode) WITH (ONLINE = ON)

',
        @database_name=N'Bocss2',
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO