检查 SQL Server 中是否存在表 [英] Check if table exists in SQL Server

问题描述

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

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.

当您在 Google 上搜索答案时，您会得到很多不同的答案.是否有官方/向后和向前兼容的方法?

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?

第一种方式:

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;

第二种方式:

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

MySQL 提供了简单的

SHOW TABLES LIKE '%tablename%'; 

声明.我正在寻找类似的东西.

statement. I am looking for something similar.

推荐答案

对于此类查询，最好使用 INFORMATION_SCHEMA 视图.这些视图(大部分)在许多不同的数据库中都是标准的，并且很少因版本而异.

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

这篇关于检查 SQL Server 中是否存在表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！