Controleer of tabel bestaat in SQL Server

Ik zou graag zien dat dit de ultieme discussie is over hoe te controleren of een tabel bestaat in SQL Server 2000/2005 met behulp van SQL-statements.

Als je Googled op het antwoord, krijg je zoveel verschillende antwoorden. Is er een officiële/achterwaarts en voorwaarts compatibele manier om dit te doen?

Hier zijn twee mogelijke manieren om dit te doen. Welke van de twee is de standaard/beste manier om dit te doen?

Eerste manier:

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;

Tweede weg:

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

MySQL biedt de eenvoudige

SHOW TABLES LIKE '%tablename%'; 

verklaring. Ik ben op zoek naar iets soortgelijks.

Antwoord 1, autoriteit 100%

Voor dit soort vragen is het altijd het beste om een ​​INFORMATION_SCHEMA-weergave te gebruiken. Deze weergaven zijn (meestal) standaard in veel verschillende databases en veranderen zelden van versie tot versie.

Om te controleren of een tafel bestaat, gebruik:

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

Antwoord 2, autoriteit 22%

Houd er ook rekening mee dat als u om welke reden dan ook moet zoeken naar een tijdelijke tafel, u dit kunt doen:

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

Antwoord 3, autoriteit 19%

We gebruiken altijd de stijl OBJECT_ID zolang ik me herinner

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

Antwoord 4, autoriteit 11%

Bekijk de onderstaande benaderingen,

Aanpak 1: de weergave INFORMATION_SCHEMA.TABLES gebruiken

We kunnen een query schrijven zoals hieronder om te controleren of er een klantentabel bestaat in de huidige database.

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

Benadering 2: de functie OBJECT_ID() gebruiken

We kunnen de OBJECT_ID()-functie zoals hieronder gebruiken om te controleren of er een klantentabel bestaat in de huidige database.

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

Aanpak 3: sys.Objects Catalogusweergave gebruiken

We kunnen de Sys.Objects-catalogusweergave gebruiken om het bestaan ​​van de tabel te controleren, zoals hieronder weergegeven:

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

Aanpak 4: sys.Tables Catalogusweergave gebruiken

We kunnen de Sys.Tables-catalogusweergave gebruiken om het bestaan ​​van de tabel te controleren, zoals hieronder weergegeven:

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

Aanpak 5: Vermijd het gebruik van sys.sysobjects Systeemtabel

We moeten vermijden om sys.sysobjects System Table rechtstreeks te gebruiken, directe toegang ertoe zal in sommige toekomstige versies van de Sql Server worden afgeschaft. Volgens de Microsoft BOL-link stelt Microsoft voor om de catalogusweergaven sys.objects/sys.tables in plaats van de systeemtabel sys.sysobjects rechtstreeks te gebruiken.

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

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

Antwoord 5, autoriteit 3%

Op zoek naar een tabel in een andere database:

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

Antwoord 6, autoriteit 2%

Ik wilde alleen een situatie noemen waarin het waarschijnlijk een beetje makkelijker zou zijn om de OBJECT_ID methode te gebruiken. De INFORMATION_SCHEMA views zijn objecten onder elke database-

De weergaven van het informatieschema zijn gedefinieerd in een speciaal schema met de naam
INFORMATION_SCHEMA. Dit schema is opgenomen in elke database.

https://msdn.microsoft.com/en-us/library/ ms186778.aspx

Daarom alle tabellen die u opent met

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;

geeft alleen weer wat zich in [database] bevindt. Als u wilt controleren of tabellen in een andere database bestaan, zonder de [database] elke keer dynamisch te wijzigen, laat OBJECT_ID u dit doen van de doos. Ex-

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

werkt net zo goed als

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

SQL SERVER 2016 Bewerken:

Vanaf 2016 heeft Microsoft de mogelijkheid vereenvoudigd om te controleren op niet-bestaande objecten voordat ze worden verwijderd, door de trefwoorden if exists toe te voegen aan drop-instructies. Bijvoorbeeld,

drop table if exists mytablename

zal hetzelfde doen als OBJECT_ID / INFORMATION_SCHEMA wrappers, in 1 regel code.

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

Antwoord 7, autoriteit 2%

IF OBJECT_ID('mytablename') IS NOT NULL 

Antwoord 8

Het gebruik van het informatieschema is de standaard SQL-manier om dit te doen, dus het moet worden gebruikt door alle databases die dit ondersteunen.

Antwoord 9

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

Hier in de bovenstaande code is de tabelnaam Mapping_APCToFANavigator.

Antwoord 10

Als u aan verschillende databases moet werken:

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

Antwoord 11

U kunt onderstaande code gebruiken

IF (OBJECT_ID('TableName') IS NOT NULL )
BEGIN
  PRINT 'Table Exists'
END
ELSE
BEGIN 
  PRINT 'Table NOT Exists'
END

Of

IF (EXISTS (SELECT * FROM sys.tables WHERE [name] = 'TableName'))
BEGIN
  PRINT 'Table Exists'
END
ELSE
BEGIN 
  PRINT 'Table NOT Exists'
END

Antwoord 12

Ik weet dat het een oude vraag is, maar ik heb deze mogelijkheid gevonden als je van plan bent hem vaak te bellen.

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

Antwoord 13

Hier even toevoegen, ten behoeve van ontwikkelaars en mede-DBA’s

een script dat @Tablename als parameter ontvangt

(die al dan niet de schemanaam bevat) en geeft de onderstaande informatie terug als de schema.table bestaat:

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

Ik heb dit script gemaakt om in andere scripts te gebruiken, elke keer als ik moet testen of een tabel of view bestaat, en als dat het geval is, de object_id voor andere doeleinden moet gebruiken.

Er wordt een fout gegenereerd wanneer u een lege tekenreeks, een verkeerde schemanaam of een verkeerde tabelnaam hebt doorgegeven.

dit kan binnen een procedure zijn en bijvoorbeeld -1 retourneren.

Als voorbeeld heb ik een tabel met de naam “Facts.FactBackOrder” in een van mijn Data Warehouse-databases.

Zo heb ik dit bereikt:

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 

Antwoord 14

In SQL Server 2000 kunt u het volgende proberen:

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
   SELECT 1 AS 'res' 
END

Antwoord 15

IF EXISTS 
(
    SELECT  * 
    FROM    INFORMATION_SCHEMA.TABLES 
    WHERE   TABLE_SCHEMA = 'PutSchemaHere'     
            AND  
            TABLE_NAME   = 'PutTableNameHere'
)

Antwoord 16

Iets belangrijks om te weten voor iedereen die zijn oplossing nog niet heeft gevonden:
SQL-server != MYSQL.
Als je het met MYSQL wilt doen, is het vrij eenvoudig

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

Ik plaats dit hier omdat het de grootste hit is bij Google.

Antwoord 17

Ik heb wat problemen gehad met het selecteren van INFORMATIONAL_SCHEME en OBJECT_ID. Ik weet niet of het een probleem is met het ODBC-stuurprogramma of zo. Vragen van SQL Management Studio waren allebei goed.

Hier is de oplossing:

SELECT COUNT(*) FROM <yourTableNameHere>

Dus als de query mislukt, is er waarschijnlijk geen dergelijke tabel in de database (of hebt u er geen toegangsrechten voor).

De controle wordt gedaan door de waarde (in mijn geval een geheel getal) te vergelijken die wordt geretourneerd door de SQL-uitvoerder die zich bezighoudt met het ODBC-stuurprogramma.

if (sqlexec(conectionHandle, 'SELECT COUNT(*) FROM myTable') == -1) {
  // myTable doesn't exist..
}

Antwoord 18

U kunt dit gebruiken:

     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

Antwoord 19

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

Antwoord 20

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

Antwoord 21

Als iemand hetzelfde probeert te doen in linq to sql (of vooral linqpad), schakel dan de optie in om systeemtabellen en views op te nemen en voer deze code uit:

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

aangezien je een object hebt met de naam in een eigenschap met de naam item en het schema in een eigenschap met de naam schema waarbij de naam van de bronvariabele a

is

Antwoord 22

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

Antwoord 23

— — procedure aanmaken om te controleren of een tabel bestaat

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 ;

— — hoe te gebruiken: controleer of er tabelmigraties bestaan ​​

 CALL checkIfTableExists('muDbName', 'migrations', @output);

Antwoord 24

ik neem hier een weergave maken als voorbeeld.

Omdat ALTER/CREATE-commando’s niet binnen BEGIN/END-blokken kunnen staan. Je moet het bestaan ​​testen en het laten vallen voordat je een creatie maakt

IF Object_ID('TestView') IS NOT NULL
DROP VIEW TestView
GO
CREATE VIEW TestView
   as
   . . .
GO

Als je je zorgen maakt dat de rechten verloren gaan, kun je de GRANT-instructies ook scripten en deze aan het einde opnieuw uitvoeren.

Je zou de create/alter in een string kunnen stoppen en een EXEC kunnen doen – dat kan lelijk worden voor grote views

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

Antwoord 25

Ik controleer altijd op deze manier.

IF OBJECT_ID('TestXML..tblCustomer') IS NOT NULL  
BEGIN  
    PRINT 'Exist'
END  
ELSE
BEGIN
    PRINT 'Not Exist'
END 

Bedankt

Antwoord 26

Als dit de ‘ultieme’ discussie is, moet worden opgemerkt dat het script van Larry Leonard ook een externe server kan opvragen als de servers zijn gekoppeld.

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

Antwoord 27

Voer deze query uit om te controleren of de tabel in de database bestaat:

IF(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'YourTableName') IS NOT NULL
PRINT 'Table Exists';

Antwoord 28

Er is nog een optie om te controleren of de tabel in alle databases bestaat

IF EXISTS(SELECT 1 FROM [change-to-your-database].SYS.TABLES WHERE NAME = 'change-to-your-table-name')
BEGIN
    -- do whatever you want
END

Antwoord 29

beschouw in één database dat je een tabel t1 hebt. u wilt een script uitvoeren op een andere database, zoals – als t1 bestaat, doe dan niets anders, maak t1.
Open hiervoor visuele studio en doe het volgende:

Klik met de rechtermuisknop op t1, dan Scripttabel als, dan DROP en Create To, dan New Query Editor

vindt u uw gewenste zoekopdracht. Maar voordat u dat script uitvoert, vergeet dan niet om de drop-instructie in de query uit te commentaaren, omdat u geen nieuwe wilt maken als die er al is.

Bedankt