Проверьте, существует ли таблица в SQL Server

Я хотел бы, чтобы это было окончательным обсуждением того, как проверить, существует ли таблица в SQL Server 2000/2005 с помощью оператора SQL.

Когда вы Google для ответа, вы получите так много разных ответов. Есть ли официальный / обратный и прямой совместимый способ сделать это?

Вот два возможных способа сделать это. Какой из двух является стандартным / лучшим способом сделать это?

Первый путь:

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%'; 

заявление. Я ищу что-то подобное.

вопрос задан 3.10.2008
Vincent
10302 репутация

22 ответов


  • 1078 рейтинг

    Для подобных запросов всегда лучше использовать представление INFORMATION_SCHEMA. Эти представления (в основном) являются стандартными для многих различных баз данных и редко меняются от версии к версии.

    Чтобы проверить, существует ли таблица, используйте:

    IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'TheSchema' 
                     AND  TABLE_NAME = 'TheTable'))
    BEGIN
        --Do Stuff
    END
    
    ответ дан akmad, с репутацией 13754, 3.10.2008
  • 228 рейтинг

    Также обратите внимание, что если по какой-либо причине вам нужно проверить временную таблицу, вы можете сделать это:

    if OBJECT_ID('tempdb..#test') is not null
     --- temp table exists
    
    ответ дан James Bloomer, с репутацией 3541, 28.01.2010
  • 185 рейтинг

    Мы всегда используем стиль OBJECT_ID, насколько я помню

    IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 
    
    ответ дан Bob King, с репутацией 20157, 3.10.2008
  • 90 рейтинг

    Пожалуйста, смотрите ниже подходы,

    Подход 1: Использование INFORMATION_SCHEMA. СТОЛЫ

    Мы можем написать запрос, как показано ниже, чтобы проверить, существует ли таблица клиентов в текущей базе данных.

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customers')
    BEGIN
        PRINT 'Table Exists'
    END
    

    Подход 2: Использование функции OBJECT_ID ()

    Мы можем использовать функцию OBJECT_ID (), как показано ниже, чтобы проверить, существует ли таблица клиентов в текущей базе данных.

    IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    BEGIN
        PRINT 'Table Exists'
    END
    

    Подход 3: Использование sys. Каталог объектов Посмотреть

    Мы можем использовать Sys. Представление каталога объектов для проверки существования таблицы, как показано ниже:

    IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_id = OBJECT_ID(N'dbo.Customers') AND Type = N'U')
    BEGIN
       PRINT 'Table Exists'
    END
    

    Подход 4: Использование sys. Каталог таблиц Смотреть

    Мы можем использовать Sys. Представление каталога таблиц для проверки существования таблицы, как показано ниже:

     IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Customers' AND Type = N'U')
     BEGIN
          PRINT 'Table Exists'
     END
    

    Подход 5: Избегайте использования sys. Системные объекты Системная таблица

    Мы должны избегать использования sys. Системная таблица sysobjects напрямую, прямой доступ к ней будет исключен в некоторых будущих версиях Sql Server. По ссылке Microsoft BOL, Microsoft предлагает использовать каталог sys. Объекты / SYS. таблицы вместо сис. Системная таблица sysobjects напрямую.

      IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Customers' AND xtype = N'U')
      BEGIN
         PRINT 'Table Exists'
      END
    

    , ссылка с: http: // sqlhints. ru / 2014/04/13 / как проверить, существует ли таблица в sql-сервере /

    ответ дан BrainCoder, с репутацией 3084, 22.12.2014
  • 30 рейтинг

    Ищем таблицу в другой базе данных:

    if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
        print 'Exists'
    
    ответ дан Larry Leonard, с репутацией 309, 15.03.2012
  • 19 рейтинг
    IF OBJECT_ID('mytablename') IS NOT NULL 
    
    ответ дан sansalk, с репутацией 2664, 22.11.2013
  • 18 рейтинг

    Просто хотел бы упомянуть одну ситуацию, в которой было бы немного проще использовать метод OBJECT_ID. INFORMATION_SCHEMA просмотров являются объектами в каждой базе данных -

    Представления информационной схемы определены в специальной схеме с именем INFORMATION_SCHEMA. Эта схема содержится в каждой базе данных.

    https: // msdn. Microsoft. ком / EN-US / библиотека / ms186778. aspx

    Поэтому все таблицы, к которым вы обращаетесь, используя

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

    будет отражать только то, что находится в [database]. Если вы хотите проверить, существуют ли таблицы в другой базе данных , без динамического изменения [database] каждый раз, OBJECT_ID позволит вам сделать это «из коробки». Пример

    IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL 
       SELECT 1 AS res ELSE SELECT 0 AS res;
    

    работает так же хорошо, как и

    IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL 
       SELECT 1 AS res ELSE SELECT 0 AS res;
    

    SQL SERVER 2016 Редактировать :

    Начиная с 2016 года, Microsoft упростила возможность проверки несуществующих объектов перед удалением, добавив ключевые слова if exists к операторам drop. Например,

    drop table if exists mytablename
    

    сделает то же самое, что и обёртки OBJECT_ID/INFORMATION_SCHEMA, в 1 строке кода.

    https: // блоги. MSDN. Microsoft. ru / sqlserverstorageengine / 2015/11/03 / drop-if-существующие-new-вещь-в-sql-server-2016/

    ответ дан iliketocode, с репутацией 5268, 22.06.2016
  • 17 рейтинг

    Использование информационной схемы - это стандартный способ SQL, поэтому ее следует использовать во всех базах данных, которые ее поддерживают.

    ответ дан Vinko Vrsalovic, с репутацией 199051, 3.10.2008
  • 10 рейтинг
    IF EXISTS 
    (
        SELECT   * 
        FROM     sys.objects 
        WHERE    object_id = OBJECT_ID(N'[dbo].[Mapping_APCToFANavigator]') 
                 AND 
                 type in (N'U')
    )
    BEGIN
    
        -- Do whatever you need to here.
    
    END
    

    Здесь в приведенном выше коде имя таблицы - Mapping_APCToFANavigator.

    ответ дан dilip kumar singh, с репутацией 109, 7.04.2011
  • 8 рейтинг

    Я знаю, что это старый вопрос, но я нашел такую ​​возможность, если вы планируете часто его называть.

    create procedure Table_Exists
    @tbl varchar(50)
    as
    return (select count(*) from sysobjects where type = 'U' and name = @tbl)
    go
    
    ответ дан dko, с репутацией 616, 21.01.2011
  • 8 рейтинг

    Если вам нужно работать на разных базах данных:

    DECLARE @Catalog VARCHAR(255)
    SET @Catalog = 'MyDatabase'
    
    DECLARE @Schema VARCHAR(255)
    SET @Schema = 'dbo'
    
    DECLARE @Table VARCHAR(255)
    SET @Table = 'MyTable'
    
    IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES   
        WHERE TABLE_CATALOG = @Catalog 
          AND TABLE_SCHEMA = @Schema 
          AND TABLE_NAME = @Table))
    BEGIN
       --do stuff
    END
    
    ответ дан Even Mien, с репутацией 20108, 21.10.2009
  • 5 рейтинг

    Просто добавляю сюда, в интересах разработчиков и коллег-администраторов баз данных

    скрипт, который получает @Tablename в качестве параметра

    (который может содержать или не содержать имя схемы) и возвращает информацию ниже, если схема. таблица существует:

    the_name                object_id   the_schema  the_table       the_type
    [Facts].[FactBackOrder] 758293761   Facts       FactBackOrder   Table
    

    Я создал этот сценарий для использования внутри других сценариев каждый раз, когда мне нужно проверить, существует ли таблица или представление, и когда это произойдет, получить его object_id для использования в других целях.

    Возникает ошибка, когда вы передали пустую строку, неверное имя схемы или неверное имя таблицы.

    это может быть внутри процедуры и возвращать -1, например.

    В качестве примера у меня есть таблица под названием «Факты». FactBackOrder "в одной из моих баз данных хранилища данных.

    Вот как я этого добился:

    PRINT 'THE SERVER IS ' + @@SERVERNAME
    --select db_name()
    PRINT 'THE DATABASE IS ' + db_NAME() 
    PRINT ''
    GO
    
    SET NOCOUNT ON
    GO
    
    --===================================================================================
    -- @TableName is the parameter
    -- the object we want to deal with (it might be an indexed view or a table)
    -- the schema might or might not be specified
    -- when not specified it is DBO
    --===================================================================================
    
    DECLARE @TableName SYSNAME
    
    SELECT @TableName = 'Facts.FactBackOrder'
    --===================================================================================
    --===================================================================================
    DECLARE @Schema SYSNAME
    DECLARE @I INT
    DECLARE @Z INT 
    
    SELECT @TableName = LTRIM(RTRIM(@TableName))
    SELECT @Z = LEN(@TableName)
    
    IF (@Z = 0) BEGIN
    
                RAISERROR('Invalid @Tablename passed.',16,1)
    
    END 
    
    SELECT @I = CHARINDEX('.',@TableName )
    --SELECT @TableName ,@I
    
    IF @I > 0 BEGIN
    
            --===================================================================================
            -- a schema and table name have been passed
            -- example Facts.FactBackOrder 
            -- @Schema = Fact
            -- @TableName = FactBackOrder
            --===================================================================================
    
       SELECT @Schema    = SUBSTRING(@TABLENAME,1,@I-1)
       SELECT @TableName = SUBSTRING(@TABLENAME,@I+1,@Z-@I)
    
    
    
    END
    ELSE BEGIN
    
            --===================================================================================
            -- just a table name have been passed
            -- so the schema will be dbo
            -- example Orders
            -- @Schema = dbo
            -- @TableName = Orders
            --===================================================================================
    
       SELECT @Schema    = 'DBO'     
    
    
    END
    
            --===================================================================================
            -- Check whether the @SchemaName is valid in the current database
            --===================================================================================
    
    IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.SCHEMATA K WHERE K.[SCHEMA_NAME] = @Schema ) BEGIN
    
                RAISERROR('Invalid Schema Name.',16,1)
    
    END 
    
    --SELECT @Schema  as [@Schema]
    --      ,@TableName as [@TableName]
    
    
    DECLARE @R1 TABLE (
    
       THE_NAME SYSNAME
      ,THE_SCHEMA SYSNAME
      ,THE_TABLE SYSNAME
      ,OBJECT_ID INT
      ,THE_TYPE SYSNAME
      ,PRIMARY KEY CLUSTERED (THE_SCHEMA,THE_NAME)
    
    )
    
    ;WITH RADHE_01 AS (
    SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.NAME) AS [the_name]
          ,the_schema=SCHEMA_NAME(O.schema_id)
          ,the_table=O.NAME
          ,object_id =o.object_id 
          ,[the_type]= CASE WHEN O.TYPE = 'U' THEN 'Table' ELSE 'View' END 
    from sys.objects O
    where O.is_ms_shipped = 0
    AND O.TYPE IN ('U','V')
    )
    INSERT INTO @R1 (
       THE_NAME 
      ,THE_SCHEMA 
      ,THE_TABLE 
      ,OBJECT_ID
      ,THE_TYPE 
    )
    SELECT  the_name
           ,the_schema
           ,the_table
           ,object_id
           ,the_type
    FROM RADHE_01
    WHERE the_schema = @Schema 
      AND the_table  = @TableName
    
    IF (@@ROWCOUNT = 0) BEGIN 
    
                 RAISERROR('Invalid Table Name.',16,1)
    
    END 
    ELSE BEGIN
    
        SELECT     THE_NAME 
                  ,THE_SCHEMA 
                  ,THE_TABLE 
                  ,OBJECT_ID
                  ,THE_TYPE 
    
        FROM @R1
    
    END 
    
    ответ дан marcello miorelli, с репутацией 1347, 23.01.2015
  • 4 рейтинг

    В SQL Server 2000 вы можете попробовать:

    IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
    BEGIN
       SELECT 1 AS 'res' 
    END
    
    ответ дан dipi evil, с репутацией 343, 16.04.2015
  • 3 рейтинг
        IF OBJECT_ID (N'dbo.T', N'U') IS NOT NULL 
          BEGIN 
              print 'deleted table';
              drop table t 
          END
        else 
          begin 
              print 'table not found' 
          end
    
    Create table t (id int identity(1,1) not null, name varchar(30) not null, lastname varchar(25) null)
    insert into t( name, lastname) values('john','doe');
    insert into t( name, lastname) values('rose',NULL);
    
    Select * from t
    1   john    doe
    2   rose    NULL
    
    -- clean
    drop table t
    
    ответ дан BTE, с репутацией 419, 18.11.2014
  • 3 рейтинг
    IF EXISTS 
    (
        SELECT  * 
    
        FROM    INFORMATION_SCHEMA.TABLES 
    
        WHERE   TABLE_SCHEMA = 'PutSchemaHere'     
                AND  
                TABLE_NAME   = 'PutTableNameHere'
    )
    
    ответ дан Moccassin, с репутацией 84, 24.10.2013
  • 2 рейтинг

    Что-то, что важно знать для тех, кто еще не нашел своего решения: SQL-сервер! = MYSQL . Если вы хотите сделать это с MYSQL , это довольно просто

        $sql = "SELECT 1 FROM `db_name`.`table_name` LIMIT 1;";
        $result = mysql_query($sql);
        if( $result == false )
            echo "table DOES NOT EXIST";
        else
            echo "table exists";
    

    Опубликовать это здесь, потому что это самый популярный сайт в Google.

    ответ дан Blauhirn, с репутацией 4638, 23.07.2014
  • 1 рейтинг
    select name from SysObjects where xType='U' and name like '%xxx%' order by name
    
    ответ дан MarceloMadnezz, с репутацией 171, 10.02.2014
  • 0 рейтинг

    - - создать процедуру, чтобы проверить, существует ли таблица


    DELIMITER $$
    
    DROP PROCEDURE IF EXISTS `checkIfTableExists`;
    
    CREATE PROCEDURE checkIfTableExists(
        IN databaseName CHAR(255),
        IN tableName CHAR(255),
        OUT boolExistsOrNot CHAR(40)
    )
    
      BEGIN
          SELECT count(*) INTO boolExistsOrNot FROM information_schema.TABLES
          WHERE (TABLE_SCHEMA = databaseName)
          AND (TABLE_NAME = tableName);
      END $$
    
    DELIMITER ;
    

    - - как использовать: проверить, существует ли миграция таблиц


     CALL checkIfTableExists('muDbName', 'migrations', @output);
    
    ответ дан Mathieu Dierckx, с репутацией 71, 6.09.2017
  • 0 рейтинг
    IF EXISTS (   SELECT * FROM   dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
    BEGIN
      SELECT * FROM dbo.TableName;
    END
    GO
    
    ответ дан Krishnaraj Barvathaya, с репутацией 307, 19.06.2018
  • 0 рейтинг

    Если это будет «окончательное» обсуждение, то следует отметить, что сценарий Ларри Леонарда может также запрашивать удаленный сервер, если серверы связаны между собой.

    if exists (select * from REMOTE_SERVER.MyOtherDatabase.sys.tables where name = 'MyTable')
        print 'Exists'
    
    ответ дан user3651072, с репутацией 1, 18.08.2014
  • 0 рейтинг

    Если кто-то пытается сделать то же самое в linq to sql (или особенно linqpad), включите опцию, чтобы включить системные таблицы и представления, и сделайте этот код:

    let oSchema = sys.Schemas.FirstOrDefault(s=>s.Name==a.schema )
    where oSchema !=null
    let o=oSchema!=null?sys.Objects.FirstOrDefault (o => o.Name==a.item && o.Schema_id==oSchema.Schema_id):null
    where o!=null
    

    , учитывая, что у вас есть объект с именем в свойстве с именем item, и схема в свойстве с именем schema, где имя исходной переменной равно a

    ответ дан Maslow, с репутацией 12317, 7.02.2014
  • -5 рейтинг

    считай в одной базе данных у тебя таблица t1. Вы хотите запустить скрипт на другой базе данных, как - если существует t1, то больше ничего не создайте t1. Для этого откройте Visual Studio и сделайте следующее:

    Щелкните правой кнопкой мыши на t1, затем на Script table as, затем на DROP и Create To, затем на New Query Editor

    .

    вы найдете нужный вам запрос. Но перед выполнением этого скрипта не забудьте закомментировать оператор drop в запросе, так как вы не хотите создавать новый, если он уже есть.

    Спасибо

    ответ дан sumon, с репутацией 13, 10.04.2013