Verificar se a tabela existe no SQL Server

Eu gostaria que esta seja a discussão final sobre como verificar se existe uma tabela no SQL Server 2000/2005 usando SQL Statement.

Quando você busca o Google, você recebe tantas respostas diferentes. Existe uma forma oficial / retroativa e direta de fazer isso?

Aqui estão duas maneiras possíveis de fazer isso. Qual dos dois é o padrão / melhor maneira de fazer isso?

Primeira maneira:

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; 

Segunda maneira:

 IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res; 

MySQL fornece o simples

 SHOW TABLES LIKE '%tablename%'; 

declaração. Eu estou procurando por algo similar.

Para consultas como esta, é sempre melhor usar uma visão INFORMATION_SCHEMA . Essas exibições são (na maioria das vezes) padrão em muitos bancos de dados diferentes e raramente mudam de versão para versão.

Para verificar se existe uma tabela use:

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

Observe também que, se por algum motivo você precisar verificar uma tabela temporária, poderá fazer isso:

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

Nós sempre usamos o estilo OBJECT_ID por tanto tempo quanto eu me lembro

 IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 

Por favor, veja as abordagens abaixo,

Abordagem 1: usando a exibição INFORMATION_SCHEMA.TABLES

Podemos escrever uma consulta como abaixo para verificar se existe uma tabela de clientes no database atual.

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

Abordagem 2: usando a function OBJECT_ID ()

Podemos usar a function OBJECT_ID () como abaixo para verificar se existe uma tabela de clientes no database atual.

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

Abordagem 3: usando a exibição do catálogo sys.Objects

Podemos usar a exibição de catálogo Sys.Objects para verificar a existência da tabela, conforme mostrado abaixo:

 IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.Customers') AND Type = N'U') BEGIN PRINT 'Table Exists' END 

Abordagem 4: usando a exibição do catálogo sys.Tables

Podemos usar a visualização do catálogo Sys.Tables para verificar a existência da tabela, conforme mostrado abaixo:

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

Abordagem 5: Evite usar a tabela de sistema sys.sysobjects

Devemos evitar o uso da tabela de sistema sys.sysobjects diretamente, o access direto a ela será descontinuado em algumas versões futuras do Sql Server. De acordo com o link Microsoft BOL, a Microsoft está sugerindo o uso das exibições do catálogo sys.objects / sys.tables em vez da tabela do sistema sys.sysobjects diretamente.

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

Referido de: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/

Procurando por uma tabela em um database diferente:

 if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable') print 'Exists' 
 IF OBJECT_ID('mytablename') IS NOT NULL 

Só queria mencionar uma situação em que provavelmente seria um pouco mais fácil usar o método OBJECT_ID . As exibições INFORMATION_SCHEMA são objects em cada database

As visualizações do esquema de informações são definidas em um esquema especial denominado INFORMATION_SCHEMA. Este esquema está contido em cada database.

https://msdn.microsoft.com/pt-br/library/ms186778.aspx

Portanto, todas as tabelas que você acessa usando

 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; 

refletirá apenas o que está em [database] . Se você quisesse verificar se existem tabelas em outro database, sem alterar dinamicamente o [database] cada vez, OBJECT_ID permitirá que você faça isso da checkbox. Ex-

 IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res; 

funciona tão bem quanto

 IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL SELECT 1 AS res ELSE SELECT 0 AS res; 

SQL SERVER 2016 Editar :

A partir de 2016, a Microsoft simplificou a capacidade de verificar objects inexistentes antes de descartar, adicionando as palavras-chave if exists para drop instruções. Por exemplo,

 drop table if exists mytablename 

fará o mesmo que os wrappers OBJECT_ID / INFORMATION_SCHEMA , em uma linha de código.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

Usar o Esquema de Informações é a maneira padrão do SQL de fazer isso, portanto, ele deve ser usado por todos os bancos de dados que o suportam.

 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 

Aqui no código acima, o nome da tabela é Mapping_APCToFANavigator .

Se você precisa trabalhar em bancos de dados diferentes:

 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 

Eu sei que é uma pergunta antiga, mas eu encontrei essa possibilidade se você planeja ligar com frequência.

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

Apenas adicionando aqui, para o benefício de desenvolvedores e colegas DBAs

um script que recebe @Tablename como um parâmetro

(que pode ou não conter o schemaname) e retorna a informação abaixo se o schema.table existir:

 the_name object_id the_schema the_table the_type [Facts].[FactBackOrder] 758293761 Facts FactBackOrder Table 

Eu produzi este script para ser usado dentro de outros scripts toda vez que eu preciso testar se uma tabela ou view existe ou não, e quando isso acontece, obtém seu object_id para ser usado para outros propósitos.

Ele gera um erro quando você passa uma string vazia, um nome de esquema incorreto ou um nome de tabela incorreto.

Isso pode estar dentro de um procedimento e retornar -1, por exemplo.

Como exemplo, tenho uma tabela chamada “Facts.FactBackOrder” em um dos meus bancos de dados do Data Warehouse.

Foi assim que consegui isso:

 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 

No SQL Server 2000, você pode tentar:

 IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME') BEGIN SELECT 1 AS 'res' END 
 IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PutSchemaHere' AND TABLE_NAME = 'PutTableNameHere' ) 
  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 

Algo importante a saber para quem ainda não encontrou sua solução: SQL server! = MYSQL . Se você quiser fazer isso com MYSQL , é bem simples

  $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"; 

Postando isso aqui porque é o maior sucesso no Google.

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

Se alguém estiver tentando fazer a mesma coisa no linq para o sql (ou especialmente no linqpad), ative a opção para include tabelas e visualizações do sistema e faça este código:

 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 

dado que você tem um object com o nome em uma propriedade chamada item, e o esquema em uma propriedade chamada esquema onde o nome da variável de origem é a

Se esta for a discussão ‘final’, então deve ser notado que o script de Larry Leonard pode consultar um servidor remoto também se os servidores estiverem ligados.

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

– – criar procedimento para verificar se existe uma tabela


 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 ; 

– – como usar: verifique se existe migration de tabelas


  CALL checkIfTableExists('muDbName', 'migrations', @output); 
 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 

considere em um database você tem uma tabela t1. você quer rodar script em outro database como – se t1 existir, então não faça mais nada t1. Para fazer isso, abra o visual studio e faça o seguinte:

Clique com o botão direito do mouse em t1, depois em Script table as, em seguida, DROP e Create To, em seguida, em New Query Editor.

você encontrará sua consulta desejada. Mas antes de executar esse script, não esqueça de comentar a instrução drop na consulta, já que você não deseja criar uma nova se já existir uma.

obrigado