Я пишу хранимую процедуру в SQL Server 2008. Мне нужно проверить, существует ли таблица в базе данных. Если этого не произойдет, мне нужно его создать.
Как мне это сделать?
Я пишу хранимую процедуру в SQL Server 2008. Мне нужно проверить, существует ли таблица в базе данных. Если этого не произойдет, мне нужно его создать.
Как мне это сделать?
Что-то вроде этого
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[YourTable](
....
....
....
)
END
Просто для сравнения, мне нравится использовать функцию object_id, как показано ниже. Это немного легче читать, и вам не нужно беспокоиться о sys.objects vs. sysobjects vs. sys.all_objects vs. sys.tables. Основная форма:
IF object_id('MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Конечно, это будет отображаться как "Present", если есть какой-либо объект с этим именем. Если вы хотите проверить только таблицы, вам понадобится:
IF object_id('MyTable', 'U') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Он также работает для временных таблиц:
IF object_id('tempdb.dbo.#MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Давайте создадим пример базы данных с таблицей по следующему сценарию:
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE dbo.tblTest (Id INT, Name NVARCHAR(50))
Подход 1. Использование представления INFORMATION_SCHEMA.TABLES
Мы можем написать запрос, как показано ниже, чтобы проверить, существует ли таблица tblTest в текущей базе данных.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
Приведенный выше запрос проверяет существование таблицы tblTest во всех схемах в текущей базе данных. Вместо этого, если вы хотите проверить существование таблицы в указанной схеме и указанной базе данных, мы можем написать приведенный выше запрос следующим образом:
IF EXISTS (SELECT * FROM Test.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
Плюсы этого подхода: представления INFORMATION_SCHEMA переносимы между разными системами RDBMS, поэтому перенос на разные RDBMS не требует каких-либо изменений.
Подход 2. Использование функции OBJECT_ID()
Мы можем использовать функцию OBJECT_ID()
, как показано ниже, чтобы проверить, существует ли таблица tblTest в текущей базе данных.
IF OBJECT_ID(N'dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Указывать части имени базы данных и имени схемы для имени таблицы необязательно. Но указание имени базы данных и имени схемы дает возможность проверить существование таблицы в указанной базе данных и в указанной схеме вместо проверки в текущей базе данных по всем схемам. Приведенный ниже запрос показывает, что, хотя текущая база данных является базой данных MASTER, мы можем проверить существование таблицы tblTest
в схеме dbo
в базе данных Test
.
USE MASTER
GO
IF OBJECT_ID(N'Test.dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Плюсы: легко запомнить. Еще одна важная особенность, которую следует упомянуть о функции OBJECT_ID()
: она предоставляет возможность проверить существование временной таблицы, которая создается в текущем контексте соединения. Все остальные подходы проверяют существование временной таблицы, созданной во всех контекстах соединений, а не только в текущем контексте соединений. Приведенный ниже запрос показывает, как проверить существование временной таблицы с помощью функции OBJECT_ID()
:
CREATE TABLE #TempTable(ID INT)
GO
IF OBJECT_ID(N'TempDB.dbo.#TempTable', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
GO
Подход 3: Использование представления каталога sys.Objects
Мы можем использовать представление каталога Sys.Objects
, чтобы проверить существование таблицы, как показано ниже:
IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.tblTest') AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Подход 4. Использование представления каталога sys.Tables
Мы можем использовать представление каталога Sys.Tables
, чтобы проверить существование таблицы, как показано ниже:
IF EXISTS(SELECT 1 FROM sys.Tables WHERE Name = N'tblTest' AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Представление каталога Sys.Tables
наследует строки из представления каталога Sys.Objects
, представление каталога Sys.objects
называется базовым представлением, где sys.Tables
называется производным представлением. Sys.Tables
будет возвращать строки только для объектов таблицы, тогда как представление Sys.Object
не только возвращает строки для объектов таблицы, но и возвращает строки для таких объектов, как: хранимая процедура, представления и т.д.
Подход 5. Избегайте использования системной таблицы sys.sysobjects
Мы должны избегать прямого использования системной таблицы sys.sysobjects
, в некоторых будущих версиях Sql Server прямой доступ к ней не рекомендуется. Согласно ссылке [Microsoft BOL] [1], Microsoft предлагает использовать представления каталога sys.objects/sys.tables
вместо системной таблицы sys.sysobjects
.
IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'tblTest' 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/
отредактированы
Вы можете посмотреть sys.tables для проверки наличия нужной таблицы:
IF NOT EXISTS (SELECT * FROM sys.tables
WHERE name = N'YourTable' AND type = 'U')
BEGIN
CREATE TABLE [SchemaName].[YourTable](
....
....
....
)
END
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'd020915'))
BEGIN
declare @result int
set @result=1
select @result as result
END
Попробуйте выполнить следующую инструкцию для проверки наличия таблицы в базе данных:
If not exists (select name from sysobjects where name = 'tablename')
Вы можете создать таблицу внутри блока if.
Declare @Username varchar(20)
Set @Username = 'Mike'
if not exists
(Select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'tblEmp')
Begin
Create table tblEmp (ID int primary key, Name varchar(50))
Print (@Username + ' Table created successfully')
End
Else
Begin
Print (@Username + ' : this Table Already exists in the database')
End
Если я не ошибаюсь, это должно работать:
if not exists (Select 1 from tableName)
create table ...