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.
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
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( )
DB_ID ( [ database-name ] )
Returns the current database ID number.
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
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
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
IF OBJECT_ID([tablename/sp name], 'U') IS NOT NULL
PRINT 'Table Exist'
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'
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.