Production deployment is performed by DBA team. As a member of development team does not have permission to access production environment. How to make sure the scripts can be successfully executed by DBA is a tricky work. Sometime the package was returned by DBA with two kind of messages: object already exist or object does not exist.
To avoid this happens, we should always check the object existence, and test the script in local environment multiple times. It is best practice to prepare rollback script in case something happens unexpectedly in production environment.
if object_id(‘dbo.table_name’) is not null drop table dbo.table_name
if object_id(‘dbo.proc_name’) is not null drop proc dbo.proc_name
if object_id(‘dbo.func_name’) is not null drop function dbo.func_name
DECLARE @jobId binary(16)
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N’job_name’)
IF (@jobId IS NOT NULL) EXEC msdb.dbo.sp_delete_job @jobId