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'
at

9 comments:

  1. AnonymousJune 11, 2008 at 11:06 AM

    thank you, this was very useful

    ReplyDelete
  2. AnonymousJuly 5, 2008 at 8:12 PM

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

    ReplyDelete
  3. AnonymousSeptember 25, 2008 at 11:06 AM

    Thanks for the information

    ReplyDelete
  4. AnonymousJanuary 23, 2009 at 5:22 AM

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

    ReplyDelete
  5. AnonymousJanuary 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'

    ReplyDelete
  6. UnknownFebruary 18, 2009 at 1:39 AM

    Thank you very much

    ReplyDelete
  7. vagelisNovember 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??

    ReplyDelete
  8. AnonymousSeptember 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

    ReplyDelete
  9. IvoNovember 5, 2011 at 5:21 AM

    @Vagelis

    substitute the PRINT with a SELECT. Enjoy =)

    Ivano

    ReplyDelete