Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statement.

When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it?

Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?

First way:

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

Second way:

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides the simple

SHOW TABLES LIKE '%tablename%'; 

statement. I am looking for something similar.

---

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

---

另外请注意，如果出于任何原因需要检查临时表，您可以这样做：

if OBJECT_ID('tempdb..#test') is not null
 --- temp table exists

---

只要我记得，我们总是使用OBJECT_ID样式

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 

链接地址: http://www.djcxy.com/p/2760.html

上一篇: 在单个SQL查询中插入多行？

下一篇: 检查SQL Server中是否存在表