Click here to Skip to main content
Click here to Skip to main content

Tagged as

Does a Database/Table/Column Exist in SQL Server

, 12 Dec 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Query to check Database, Table, Column exists 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

♥…ЯҠ…♥
Software Developer
India India
There are only 10 type of people in this programming world....
one who knows the binary and other who doesn't.

Comments and Discussions

 
GeneralMy vote of 3 PinmemberMember 1076371723-Nov-14 17:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 12 Dec 2013
Article Copyright 2013 by ♥…ЯҠ…♥
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid