December 19, 2007

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'


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

9 Responses to "Check if a database or table exists using Sql Server 2005"
  1. Anonymous said...
    June 11, 2008 at 11:06 AM

    thank you, this was very useful

  2. Anonymous said...
    July 5, 2008 at 8:12 PM

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

  3. Anonymous said...
    September 25, 2008 at 11:06 AM

    Thanks for the information

  4. Anonymous said...
    January 23, 2009 at 5:22 AM

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

  5. Anonymous said...
    January 23, 2009 at 5:24 AM

    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'

  6. Senthil said...
    February 18, 2009 at 1:39 AM

    Thank you very much

  7. vagelis said...
    November 7, 2009 at 2:11 AM

    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??

  8. Anonymous said...
    September 8, 2010 at 3:32 PM

    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

  9. ReNegaDE said...
    November 5, 2011 at 5:21 AM

    @Vagelis

    substitute the PRINT with a SELECT. Enjoy =)

    Ivano

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions