질문자 :Vincent
SQL 문을 사용하여 SQL Server 2000/2005에 테이블이 있는지 확인하는 방법에 대한 궁극적인 토론이 되었으면 합니다.
구글링하면 답이 정말 다양합니다. 공식/이전 및 앞으로 호환 가능한 방법이 있습니까?
여기에 두 가지 가능한 방법이 있습니다. 둘 중 어느 것이 표준/가장 좋은 방법입니까?
첫 번째 방법:
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%';성명. 나는 비슷한 것을 찾고 있습니다.
이와 같은 쿼리의 경우 항상 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 보기 사용
현재 데이터베이스에 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.sysobjects 시스템 테이블을 직접 사용하는 것을 피해야 합니다. 직접 액세스하는 것은 Sql Server의 일부 향후 버전에서 더 이상 사용되지 않습니다. Microsoft BOL 링크에 따라 Microsoft는 sys.sysobjects 시스템 테이블 대신 카탈로그 뷰 sys.objects/sys.tables를 직접 사용할 것을 제안합니다.
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 서버 2016 편집 :
if exists 키워드를 drop 문에 추가하여 삭제하기 전에 존재하지 않는 개체를 확인하는 기능을 단순화했습니다. 예를 들어,
drop table if exists mytablename 한 줄의 코드에서 OBJECT_ID / INFORMATION_SCHEMA 래퍼와 동일한 작업을 수행합니다.
IF OBJECT_ID('mytablename') IS NOT NULL
정보 스키마를 사용하는 것은 이를 수행하는 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아래 코드를 사용할 수 있습니다
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나는 그것이 오래된 질문이라는 것을 알고 있지만 자주 전화할 계획이라면 이 가능성을 찾았습니다.
create procedure Table_Exists @tbl varchar(50) as return (select count(*) from sysobjects where type = 'U' and name = @tbl) go개발자와 동료 DBA의 이익을 위해 여기에 추가하기만 하면 됩니다.
@Tablename을 매개변수로 받는 스크립트
(스키마 이름을 포함하거나 포함하지 않을 수 있음) schema.table이 있는 경우 아래 정보를 반환합니다.
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 ENDSQL Server 2000 에서는 다음을 시도할 수 있습니다.
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME') BEGIN SELECT 1 AS 'res' ENDIF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PutSchemaHere' AND TABLE_NAME = 'PutTableNameHere' )아직 솔루션을 찾지 못한 사람이 알아야 할 중요한 사항: 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에서 최고 히트작이기 때문에 여기에 게시합니다.
INFORMATIONAL_SCHEME 및 OBJECT_ID 중에서 선택하는 데 몇 가지 문제가 있었습니다. ODBC 드라이버 문제인지 아니면 다른 문제인지 모르겠습니다. SQL Management Studio의 쿼리는 둘 다 괜찮았습니다.
해결책은 다음과 같습니다.
SELECT COUNT(*) FROM <yourTableNameHere>따라서 쿼리가 실패하면 데이터베이스에 그러한 테이블이 없을 것입니다(또는 액세스 권한이 없는 경우).
확인은 ODBC 드라이버를 처리하는 SQL executor에서 반환된 값(내 경우에는 정수)을 비교하여 수행됩니다.
if (sqlexec(conectionHandle, 'SELECT COUNT(*) FROM myTable') == -1) { // myTable doesn't exist.. }다음을 사용할 수 있습니다.
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 tIF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) BEGIN SELECT * FROM dbo.TableName; END GOIF 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누군가 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이라는 속성에 이름이 있는 객체가 있고 소스 변수 이름이 a
select name from SysObjects where xType='U' and name like '%xxx%' order by name
-- -- 테이블이 존재하는지 확인하는 프로시저 생성
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 문도 스크립트로 작성하고 마지막에 다시 실행할 수 있습니다.
생성/변경을 문자열로 래핑하고 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저는 항상 이렇게 확인합니다.
IF OBJECT_ID('TestXML..tblCustomer') IS NOT NULL BEGIN PRINT 'Exist' END ELSE BEGIN PRINT 'Not Exist' END감사 해요
이것이 '궁극의' 논의가 되려면 Larry Leonard의 스크립트가 서버가 연결된 경우 원격 서버도 쿼리할 수 있다는 점에 유의해야 합니다.
if exists (select * from REMOTE_SERVER.MyOtherDatabase.sys.tables where name = 'MyTable') print 'Exists'이 쿼리를 실행하여 데이터베이스에 테이블이 있는지 확인합니다.
IF(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'YourTableName') IS NOT NULL PRINT 'Table Exists';데이터베이스에 테이블이 있는지 확인하는 옵션이 하나 더 있습니다.
IF EXISTS(SELECT 1 FROM [change-to-your-database].SYS.TABLES WHERE NAME = 'change-to-your-table-name') BEGIN -- do whatever you want END하나의 데이터베이스에 테이블 t1이 있다고 생각하십시오. 다른 데이터베이스에서 스크립트를 실행하려는 경우 - t1이 존재하면 다른 작업을 수행하지 않고 t1을 생성합니다. 이렇게 하려면 Visual Studio를 열고 다음을 수행합니다.
t1을 마우스 오른쪽 버튼으로 클릭한 다음 Script table as, DROP 및 Create To, New Query Editor를 차례로 클릭합니다.
원하는 쿼리를 찾을 수 있습니다. 그러나 해당 스크립트를 실행하기 전에 쿼리에서 drop 문을 주석 처리하는 것을 잊지 마십시오. 이미 있는 경우 새로 만들고 싶지 않기 때문입니다.
감사 해요
출처 : http:www.stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server