How to test the existence of a database object ?

OBJECT_ID  is used to check the existence of the table and DB_ID is used to check the existence of the Database.

Syntax of the OBJECT_ID function

<pre class="brush:sql;">

OBJECT_ID(‘object/table’)

</pre> 


Syntax of the DB_ID function

<pre class="brush:sql;">

DB_ID(‘database’)

</pre> 

EXAMPLES:

• Code that tests whether a database exists before it deletes it

<pre class="brush:sql;">

IF DB_ID(‘Test_DB’) IS NOT NULL

DROP DATABASE Test_DB

CREATE DATABASE Test_DB

</pre>

• Code that tests for the existence of the talbe

<pre class="brush:sql;">

IF OBJECT_ID(‘Test_Table’) IS NOT NULL

DROP TABLE Test_Table

</pre>

• Another way to test the existence of a table

<pre class="brush:sql;">

IF EXISTS ( SELECT * FROM sys.tables where name = ‘Test_Table’)

DROP TABLE Test_Table

</pre>

• Code that tests for the existence of the temp table

<pre class="brush:sql;">

IF OBJECT_ID(‘tempdb..#temptable_name’) IS NOT NULL

DROP TABLE #temptable_name

</pre>