Piszę procedurę składowaną w SQL Server 2008. Muszę sprawdzić, czy tabela istnieje w bazie danych. Jeśli tak nie jest, muszę go utworzyć.
Jak mam to zrobic?
Piszę procedurę składowaną w SQL Server 2008. Muszę sprawdzić, czy tabela istnieje w bazie danych. Jeśli tak nie jest, muszę go utworzyć.
Jak mam to zrobic?
CREATE TABLE IF NOT EXISTS ...
Odpowiedzi:
Coś takiego
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
if (not exists (select object_id from sys.objects where object_id = OBJECT_ID(N'[dbo].[client_tgi_g67_period_list]') and type = 'U'))
Dla kontrastu lubię używać funkcji object_id, jak pokazano poniżej. Jest trochę łatwiejszy do odczytania i nie musisz się martwić o sys.objects, sysobjects, sys.all_objects i sys.tables. Podstawowa forma:
IF object_id('MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Oczywiście będzie to pokazać jako „Present”, jeśli istnieje jakikolwiek obecny obiekt o tej nazwie. Jeśli chcesz sprawdzić tylko tabele, potrzebujesz:
IF object_id('MyTable', 'U') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Działa również w przypadku tabel tymczasowych:
IF object_id('tempdb.dbo.#MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Utwórzmy przykładową bazę danych z tabelą według poniższego skryptu:
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE dbo.tblTest (Id INT, Name NVARCHAR(50))
Podejście 1: Korzystanie z widoku INFORMATION_SCHEMA.TABLES
Możemy napisać zapytanie jak poniżej, aby sprawdzić, czy tabela tblTest istnieje w bieżącej bazie danych.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
Powyższe zapytanie sprawdza istnienie tabeli tblTest we wszystkich schematach w bieżącej bazie danych. Zamiast tego, jeśli chcesz sprawdzić istnienie tabeli w określonym schemacie i określonej bazie danych, możemy napisać powyższe zapytanie jak poniżej:
IF EXISTS (SELECT * FROM Test.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
Zalety tego podejścia: widoki INFORMATION_SCHEMA można przenosić między różnymi systemami RDBMS, więc przenoszenie do różnych systemów RDBMS nie wymaga żadnych zmian.
Podejście 2: Korzystanie z funkcji OBJECT_ID ()
Możemy użyć OBJECT_ID()
funkcji jak poniżej, aby sprawdzić, czy tabela tblTest istnieje w bieżącej bazie danych.
IF OBJECT_ID(N'dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Określenie części nazwy bazy danych i nazwy schematu dla nazwy tabeli jest opcjonalne. Jednak określenie nazwy bazy danych i nazwy schematu umożliwia sprawdzenie istnienia tabeli w określonej bazie danych i w określonym schemacie, zamiast sprawdzania bieżącej bazy danych we wszystkich schematach. Poniższe zapytanie pokazuje, że mimo że aktualna baza danych jest bazą MASTER, możemy sprawdzić istnienie tblTest
tabeli w dbo
schemacie w Test
bazie danych.
USE MASTER
GO
IF OBJECT_ID(N'Test.dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Zalety: łatwe do zapamiętania. Kolejną godną uwagi kwestią, o której należy wspomnieć o OBJECT_ID()
funkcji, jest: zapewnia opcję sprawdzenia istnienia tabeli tymczasowej, która jest tworzona w kontekście bieżącego połączenia. Wszystkie inne podejścia sprawdzają istnienie tabeli tymczasowej utworzonej we wszystkich kontekstach połączeń, a nie tylko w bieżącym kontekście połączenia. Poniższe zapytanie pokazuje, jak sprawdzić istnienie tabeli tymczasowej za pomocą OBJECT_ID()
funkcji:
CREATE TABLE #TempTable(ID INT)
GO
IF OBJECT_ID(N'TempDB.dbo.#TempTable', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
GO
Podejście 3: Korzystanie z widoku katalogu sys.Objects
Możemy użyć Sys.Objects
widoku katalogu, aby sprawdzić istnienie tabeli, jak pokazano poniżej:
IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.tblTest') AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Podejście 4: Korzystanie z widoku katalogu sys.Tables
Możemy użyć Sys.Tables
widoku katalogu, aby sprawdzić istnienie tabeli, jak pokazano poniżej:
IF EXISTS(SELECT 1 FROM sys.Tables WHERE Name = N'tblTest' AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Sys.Tables
widok katalogu dziedziczy wiersze z Sys.Objects
widoku katalogu, widok Sys.objects
katalogu jest określany jako widok bazowy, gdzie sys.Tables
jest nazywany widokiem pochodnym. Sys.Tables
zwróci wiersze tylko dla obiektów tabeli, podczas gdy Sys.Object
view oprócz wierszy dla obiektów tabeli zwróci wiersze dla obiektów takich jak: procedura składowana, widoki itp.
Podejście 5: Unikaj używania sys.sysobjects Tabela systemowa
Powinniśmy unikać sys.sysobjects
bezpośredniego korzystania z tabeli systemowej, bezpośredni dostęp do niej będzie przestarzały w niektórych przyszłych wersjach Sql Server. Zgodnie z odsyłaczem [Microsoft BOL] [1], Microsoft sugeruje użycie widoków katalogu sys.objects/sys.tables
zamiast sys.sysobjects
bezpośrednio tabeli systemowej.
IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'tblTest' AND xtype = N'U')
BEGIN
PRINT 'Table Exists'
END
Źródła: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'd020915'))
BEGIN
declare @result int
set @result=1
select @result as result
END
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
Wypróbuj następującą instrukcję, aby sprawdzić istnienie tabeli w bazie danych:
If not exists (select name from sysobjects where name = 'tablename')
Możesz utworzyć tabelę wewnątrz bloku if.
sysobjects
jest to widok zgodności, który istnieje tylko po to, aby uniknąć uszkodzenia starszego kodu. Moja sugestia byłoby użycie widoków katalogu systemowego (np sys.objects
, sys.tables
) dla kodu, który będzie kierowanych wyłącznie instancji SQL Server 2008 oraz widokiem schematu informacje (np information_schema.tables
) do kodu, który musi być przenośny. Więcej informacji na temat różnych widoków można znaleźć tutaj: Wykonywanie zapytań do katalogu systemu SQL Server
Jeśli się nie mylę, to powinno działać:
if not exists (Select 1 from tableName)
create table ...