sql-server - 检查SQL Server中是否存在表(Check if table exists in SQL Server)

Question

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

(我希望这是关于如何使用SQL语句检查SQL Server 2000/2005中是否存在表的最终讨论。)

When you Google for the answer, you get so many different answers.

(当您用Google搜索答案时，会得到很多不同的答案。)

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

(MySQL提供的简单)

SHOW TABLES LIKE '%tablename%'; 

statement.

(声明。)

I am looking for something similar.

(我正在寻找类似的东西。)

  ask by Vincent translate from so

Answer 1

For queries like this it is always best to use an INFORMATION_SCHEMA view.

(对于此类查询，最好始终使用INFORMATION_SCHEMA视图。)

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