Does a Database/Table/Column Exist in SQL Server

Introduction

In Database if we try to create a new Database,new Table, or new Column without knowing the existence of it will lead to error it will create some frustration if you cannot a anyone of the above listed with a name that you intended to use.This tip will surely help you to overcome this frustration and you can check whether database server has the database or Table already exist before creating or accessing.

Background

When I was instructed to access database in remote server, no one knows whether the database exists in remote server or not, without knowing how can I access the database? This question helped me to write this tip to verify the existence of the Database.

Using the code

As I said, without knowing the existence of Database or table in remote server its not safe to access/create it.

To check the existence of Database in DB server you can use DB_ID( ) function

Syntax

DB_ID ( [ database-name ] )  

Returns the current database ID number.

PRINT DB_ID(); 

Will return ID of the Database that is running

Pass database-name as parameter to the funtion like this

IF DB_ID('master')IS NOT NULL PRINT 'DataBase Exists,Make use of it' ELSE CREATE DATABASE master 

If you execute the above query in sql server then you will end up with this following error

Msg 1801, Level 16, State 3, Line 1 
Database 'master' already exists. Choose a different database name.  

Since by default all DB server has master DB.

IF DB_ID('PrabakarDB')IS NOT NULL PRINT 'DataBase Exists' ELSE CREATE DATABASE PrabakarDB

Now PrabakarDB is created in your sql server. You can also use DROP command instead of CREATE

IF DB_ID('PrabakarDB')IS NOT NULL DROP Database PrabakarDB ELSE    CREATE DATABASE PrabakarDB 

Similarly you can check for table/stored procedure as well using the below query

Syntax

IF OBJECT_ID([tablename/sp name], 'U') IS NOT NULL 
  PRINT 'Table Exist' 
ELSE 
  PRINT 'Table does not exist' 

U - Denotes user defined table in sql server. For complete list see here

For Column existence validation you can use

IF COL_LENGTH('tblStudent','StudentID') IS NOT NULL 
  PRINT 'Column exists'
ELSE    
  PRINT 'Column does not exist'       

Hope this tips helps you a bit to identify and reduce the time to name the DB/Table/Column in SQL Server.