Query to Check SP/Table/Trigger Exists in Database or not

We often need create/update Stored Procedure,Tables or Triggers in database via SQL scripts. It is a best practice to check if these objects exist in the database before create/update them.

Let's look at several examples

1. Checking whether procedure exist in the NORTHWIND database with name 'CustOrdersDetail'

Approach -1:

use NORTHWIND

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

print 'CustOrdersDetail Exists in the NORTHWIND database'

else

print 'CustOrdersDetail *does not exist* in the NORTHWIND database'

Approach -2:

Use NORTHWIND

if exists(select * from dbo.sysobjects where type = 'p' and name = 'CustOrdersDetail’ )

print ''CustOrdersDetail Exists in the NORTHWIND database'

else

print ''CustOrdersDetail *DOES not exist* in the NORTHWIND database'

2. Checking whether table exist in the database with name 'EmployeeTerritories'

Approach -1:

use NORTHWIND

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployeeTerritories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

print 'EmployeeTerritories Exists in the NORTHWIND database'

else

print 'EmployeeTerritories *DOES not exist* in the NORTHWIND database'

Approach -2:

use NORTHWIND

if exists(select * from dbo.sysobjects where type = 'U' and name = 'EmployeeTerritories' )

print 'EmployeeTerritories Exists in the NORTHWIND database'

else

print 'EmployeeTerritories *DOES not exist* in the NORTHWIND database'

3. Checking whether trigger exist in the database with name 'TempTrigger'

Approach -1:

use Northwind

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempTrigger]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)

print 'TempTrigger Exists in the NORTHWIND database'

else

print 'TempTrigger *DOES not exist* in the NORTHWIND database'

Approach -2:

use Northwind

if exists (select * from dbo.sysobjects where name ='TempTrigger' and type ='TR')

print 'TempTrigger Exists in the NORTHWIND database'

else

print 'TempTrigger *DOES not exist* in the NORTHWIND database'

[@more@]