Проверьте, существует ли таблица в 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%';
заявление. Я ищу что-то подобное.
32 ответа
Для таких запросов всегда лучше использовать INFORMATION_SCHEMA Посмотреть. Эти представления (в основном) являются стандартными для многих различных баз данных и редко меняются от версии к версии.
Чтобы проверить, существует ли таблица, используйте:
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
Пожалуйста, смотрите ниже подходы,
Подход 1: Использование представления INFORMATION_SCHEMA.TABLES
Мы можем написать запрос, как показано ниже, чтобы проверить, существует ли таблица клиентов в текущей базе данных.
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.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.sysobjects напрямую, прямой доступ к ней будет исключен в некоторых будущих версиях Sql Server. Что касается ссылки Microsoft BOL, Microsoft предлагает использовать представления каталога sys.objects/sys.tables вместо системной таблицы sys.sysobjects напрямую.
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 строку кода.
Вы можете использовать код ниже
IF (OBJECT_ID('TableName') IS NOT NULL )
BEGIN
PRINT 'Table Exists'
END
ELSE
BEGIN
PRINT 'Table NOT Exists'
END
Или
IF (EXISTS (SELECT * FROM sys.tables WHERE [name] = 'TableName'))
BEGIN
PRINT 'Table Exists'
END
ELSE
BEGIN
PRINT 'Table NOT Exists'
END
Использование информационной схемы - это стандартный способ SQL, поэтому она должна использоваться всеми базами данных, которые ее поддерживают.
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,
Если вам нужно работать с разными базами данных:
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
Я знаю, что это старый вопрос, но я нашел такую возможность, если вы планируете часто звонить.
create procedure Table_Exists
@tbl varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @tbl)
go
В SQL Server 2000 вы можете попробовать:
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
SELECT 1 AS 'res'
END
Просто добавив сюда, в интересах разработчиков и коллег-администраторов баз данных
скрипт, который получает @Tablename в качестве параметра
(которая может содержать или не содержать имя схемы) и возвращает информацию ниже, если существует схема:
the_name object_id the_schema the_table the_type
[Facts].[FactBackOrder] 758293761 Facts FactBackOrder Table
Я создал этот сценарий для использования внутри других сценариев каждый раз, когда мне нужно проверить, существует ли таблица или представление, и когда это произойдет, получить его object_id для использования в других целях.
Возникает ошибка, когда вы передали пустую строку, неверное имя схемы или неверное имя таблицы.
это может быть внутри процедуры и возвращать -1, например.
Например, у меня есть таблица с именем "Facts.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
IF EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PutSchemaHere'
AND
TABLE_NAME = 'PutTableNameHere'
)
Я всегда так проверяю.
IF OBJECT_ID('TestXML..tblCustomer') IS NOT NULL
BEGIN
PRINT 'Exist'
END
ELSE
BEGIN
PRINT 'Not Exist'
END
благодаря
Что-то, что важно знать всем, кто еще не нашел своего решения: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.
Я думаю, что следующий запрос работает:
IF EXISTS (select * from sys.tables
WHERE name='mytablename' )
BEGIN
print 'table exists in the database'
END
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
У меня были некоторые проблемы с выбором из INFORMATIONAL_SCHEME и OBJECT_ID. Я не знаю, проблема ли это в драйвере ODBC или что-то в этом роде. Запросы из студии управления SQL оба были в порядке.
Вот решение:
SELECT COUNT(*) FROM <yourTableNameHere>
Таким образом, если запрос не выполняется, вероятно, в базе данных нет такой таблицы (или у вас нет прав доступа к ней).
Проверка выполняется путем сравнения значения (в моем случае целочисленного), возвращаемого исполнителем SQL, который работает с драйвером ODBC.
if (sqlexec(conectionHandle, 'SELECT COUNT(*) FROM myTable') == -1) {
// myTable doesn't exist..
}
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
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_CATALOG = 'Database Name' and
TABLE_NAME = 'Table Name' and
TABLE_SCHEMA = 'Schema Name') -- Database and Schema name in where statement can be deleted
BEGIN
--TABLE EXISTS
END
ELSE BEGIN
--TABLE DOES NOT EXISTS
END
- создать процедуру, чтобы проверить, существует ли таблица
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);
Я беру здесь создание представления в качестве примера.
Потому что команды ALTER/CREATE не могут находиться внутри блоков BEGIN/END. Вам нужно проверить существование и отбросить его, прежде чем создавать
IF Object_ID('TestView') IS NOT NULL
DROP VIEW TestView
GO
CREATE VIEW TestView
as
. . .
GO
Если вас беспокоит потеря разрешений, вы также можете написать сценарий GRANT и повторно запустить его в конце.
Вы можете обернуть create/alter в строку и выполнить EXEC - это может стать некрасивым для больших представлений
DECLARE @SQL as varchar(4000)
-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE'
IF Object_ID('TestView') IS NULL
SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE
SET @SQL = 'ALTER VIEW TestView AS ' + @SQL
select name from SysObjects where xType='U' and name like '%xxx%' order by name
Есть еще одна возможность проверить, существует ли таблица в базах данных.
IF EXISTS(SELECT 1 FROM [change-to-your-database].SYS.TABLES WHERE NAME = 'change-to-your-table-name')
BEGIN
-- do whatever you want
END
Если кто-то пытается сделать то же самое в 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
почему большинство пользователей говорят: «если *** не равно нулю»!?, попробуйте «если *** равно нулю» :)
IF OBJECT_ID(N'[dbo].[Error]', N'U') IS null
BEGIN
CREATE TABLE [dbo].[Error](
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Values] [nvarchar](MAX) NOT NULL,
[Created] [datetimeoffset](0) NOT NULL,
)
END
Запустите этот запрос, чтобы проверить, существует ли таблица в базе данных:
IF(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'YourTableName') IS NOT NULL
PRINT 'Table Exists';
Если это будет "окончательное" обсуждение, то следует отметить, что сценарий Ларри Леонарда может также запрашивать удаленный сервер, если серверы связаны между собой.
if exists (select * from REMOTE_SERVER.MyOtherDatabase.sys.tables where name = 'MyTable')
print 'Exists'