Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
1

How do I determine if a table exists in a SQL Server database in SQL Server 2008?

share|improve this question

2 Answers

up vote 12 down vote accepted

If you query the sysobjects table, with a query like

SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'yourTableName'

xtype = 'U' is a user table

you can then wrap this is an IF EXISTS statement

IF EXISTS (SELECT * FROM sysobjects ...)
BEGIN
    ' do your stuff here if it exists
END
share|improve this answer
2  
+1 It is important to note that Microsoft has been moving functionality like this to Dynamic Management Views (DMVs) since SQL Server 2005. This will technically work, but Microsoft now recommends using the sys.tables DMV for this. IF EXISTS(SELECT * FROM sys.tables WHERE type = 'U' AND name = 'yourTableName'). Functionally, your statement and my statement do the exact same thing. Just wanted to mention DMVs. To learn more about DMVs, check out the documentation msdn.microsoft.com/en-US/library/ms188754%28v=SQL.105%29.aspx – Matt M Jan 27 '11 at 13:12
That's a good call Matt. Old habits die hard :-) Yours is the better way. – Miles D Jan 27 '11 at 15:02
I'm not sure if it makes much difference but I'd favour IF EXISTS (SELECT 1 FROM sys.tables...) – David Hayes Aug 3 '11 at 20:06
up vote 7 down vote

Here is one more way of finding it

IF OBJECT_ID('tablename') IS NULL
PRINT 'Table Does not Exist'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.