Check if a database or table exists using Sql Server 2005

The Object_ID() function in SQL Server can be utilised in a number of ways. One such utility is to verify if an object exists.

The Object_ID() takes in the object name and object type as parameters. The object name is the object used and the object type is the type of object used in a schema.

For example to check if a table exists in a database, use this query :

IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
Print 'Table Exists'
ELSE
Print 'Table Does Not Exists'

where 'AdventureWorks.dbo.AWBuildVersion' is the object name and 'U' is the object type which represents a table

Similarly you can check for a stored procedure or a view by specifying the correct object type. You can get an entire list of object types over here.

To check if a database exists, you can use the DB_ID() function as shown below :

IF db_id('AdventureWorks') IS NOT NULL
Print 'Database Exists'
ELSE
Print 'Database Does Not Exists'


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

9 comments:

Anonymous said...

thank you, this was very useful

Anonymous said...

Thanks, I was also looking for the same thing. Thank you.

Anonymous said...

Thanks for the information

Anonymous said...

Done this before, am having a blonde day so this was very helpful thanks!

Anonymous said...

Very useful, you can also do this if youre running across several db's in a cursor or something.

declare @db_name varchar(255)
set @db_name = 'db_test'
/*only run in dbs that have view*/
IF OBJECT_ID (@db_name+'.dbo.custom_count_dbm_mth2_vw','V') IS NOT NULL
Print 'do this'
ELSE
Print 'do nothing or something else'

Unknown said...

Thank you very much

vagelis said...

hello
i have a problem with those if statements in sql server

i call that query with vb.net

IF OBJECT_ID ('hn.dbo.AWBuildVersion','U') IS NOT NULL print 'table exists' ELSE Print 'Table Does Not Exists'

and i reseave the message
the read try is not valid because it doesnt exist data

can anyone help??

Anonymous said...

Most Excellent, thank you. I hope it's clear to everyone that this is a query that you'd be running on the server itself, and that we who are running VB. code against the server are going to construct these as a query strings that we'd be running from a command object (or however you choose to run queries). This seems well suited to be made as a stored procedure, pass it the table name, perhaps object type and schema and have the result returned. I'll post that once I figure it out.
Sorry I'm contributing over two years after the rest of you...
Best Regards

Ivo said...

@Vagelis

substitute the PRINT with a SELECT. Enjoy =)

Ivano