sql-server - существование - проверить наличие таблицы ms sql




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

    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

Я хотел бы, чтобы это было окончательное обсуждение того, как проверить, существует ли таблица в 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%'; 

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


- - создать процедуру для проверки наличия таблицы

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);

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

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

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

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

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

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

if exists (select * from REMOTE_SERVER.MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'

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


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

Подход 1: Использование вида INFORMATION_SCHEMA.TABLES

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

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

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

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

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

Подход 3: Использование представления каталога sys.Objects

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

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.Tables

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

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

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

Нам следует избегать непосредственного использования системной таблицы sys.ssosobjects, прямой доступ к ней будет устаревшим в некоторых будущих версиях Sql-сервера. По ссылке Microsoft BOL Microsoft предлагает использовать представления каталога sys.objects / sys.tables вместо системной таблицы sys.ssosobjects напрямую.

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

ссылка: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/


Поиск таблицы в другой базе данных:

if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'

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

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

https://msdn.microsoft.com/en-us/library/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://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/


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

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

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

create procedure Table_Exists
@tbl varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @tbl)
go

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

Щелкните правой кнопкой мыши на t1, затем выберите таблицу сценариев, затем DROP и Create To, затем New Query Editor

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

Спасибо


IF EXISTS 
(
    SELECT  * 

    FROM    INFORMATION_SCHEMA.TABLES 

    WHERE   TABLE_SCHEMA = 'PutSchemaHere'     
            AND  
            TABLE_NAME   = 'PutTableNameHere'
)

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

select name from SysObjects where xType='U' and name like '%xxx%' order by name




sql-server-2000