Preparing SQL script to check object existance for production deployment

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.

table:

if object_id('dbo.table_name') is not null drop table dbo.table_name

stored procedure:

if object_id('dbo.proc_name') is not null drop proc dbo.proc_name

function:

if object_id('dbo.func_name') is not null drop function dbo.func_name

job schedule:

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

Author: IT Team