How do I determine if a table exists in a SQL Server database in SQL Server 2008?
Add a comment
|
3 Answers
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
-
5+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 MJan 27, 2011 at 13:12
-
That's a good call Matt. Old habits die hard :-) Yours is the better way.– Miles DJan 27, 2011 at 15:02
-
1I'm not sure if it makes much difference but I'd favour IF EXISTS (SELECT 1 FROM sys.tables...) Aug 3, 2011 at 20:06
-
@DavidHayes Yes, I guess this solution is faster because it does not query to find column names.– XriukFeb 4, 2018 at 18:19
Here is one more way of finding it
IF OBJECT_ID('tablename') IS NULL
PRINT 'Table Does not Exist'
-
Nice and simple, no need to query complex system tables. Thanks! Nov 29, 2016 at 14:45
-
2Wouldn't that detect, for example, views of the same name? Perhaps stored procedures and such as well?– MarioDSDec 21, 2016 at 14:24
-
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your table name here')
BEGIN
PRINT 'Table Exists'
END
