Check if table exists in SQL Server

Question

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000 2005 using SQL Statements   When you Google for the answer  you get so many different answers  Is there an official backward and forward compatible way of doing it   Here are two possible ways of doing it  Which one among the two is the standard best way of doing it   First way   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    Second way   IF OBJECT ID  N mytablename   N U   IS NOT NULL     SELECT 1 AS res ELSE SELECT 0 AS res    MySQL provides the simple   SHOW TABLES LIKE   tablename       statement  I am looking for something similar

User · Answer

If you need to work on different databases   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

User · Answer

I know it is an old question but I have found this possibility if you plan to call it often   create procedure Table Exists  tbl varchar 50  as return  select count    from sysobjects where type    U  and name    tbl  go

User · Answer

IF EXISTS        SELECT          FROM    INFORMATION SCHEMA TABLES       WHERE   TABLE SCHEMA    PutSchemaHere                   AND               TABLE NAME      PutTableNameHere

User · Answer

If this is to be the  ultimate  discussion  then it should be noted that Larry Leonard s script can query a remote server as well if the servers are linked   if exists  select   from REMOTE SERVER MyOtherDatabase sys tables where name    MyTable       print  Exists

User · Answer

Also note that if for any reason you need to check for a temporary table you can do this   if OBJECT ID  tempdb   test   is not null  --- temp table exists

User · Answer

Something important to know for anybody who hasn t found their solution yet  SQL server    MYSQL  If you want to do it with MYSQL  it is quite simple       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     Posting this here because it s the top hit at Google

User · Answer

There is one more option to check if the table exists across databases  IF EXISTS SELECT 1 FROM  change-to-your-database  SYS TABLES WHERE NAME    change-to-your-table-name   BEGIN     -- do whatever you want END

User · Answer

Just adding here  for the benefit of developers and fellow DBAs  a script that receives  Tablename as a parameter    which may or may not contain the schemaname  and returns the info below if the schema table exists   the name                object id   the schema  the table       the type  Facts   FactBackOrder  758293761   Facts       FactBackOrder   Table   I produced this script to be used inside other scripts every time I need to test whether or not a table or view exists  and when it does  get its object id to be used for other purposes   It raises an error when either you passed an empty string  wrong schema name or wrong table name   this could be inside a procedure and return -1 for example   As an example  I have a table called  Facts FactBackOrder  in one of my Data Warehouse databases   This is how I achieved this   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  gt  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

User · Answer

In SQL Server 2000 you can try   IF EXISTS SELECT 1 FROM sysobjects WHERE type    U  and name    MYTABLENAME   BEGIN    SELECT 1 AS  res   END

User · Answer

Run this query to check if the table exists in the database   IF SELECT TABLE NAME from INFORMATION SCHEMA TABLES where TABLE NAME    YourTableName   IS NOT NULL PRINT  Table Exists

User · Answer

IF OBJECT ID  mytablename   IS NOT NULL

User · Answer

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   Here in the above code  the table name is Mapping APCToFANavigator

User · Answer

Just wanted to mention one situation where it would probably be a little easier to use the OBJECT ID method   The INFORMATION SCHEMA views are objects under each database-     The information schema views are defined in a special schema named   INFORMATION SCHEMA  This schema is contained in each database    https   msdn microsoft com en-us library ms186778 aspx  Therefore all tables you access using   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    will only reflect what is in  database    If you wanted to check if tables in another database exist  without dynamically changing the  database  each time  OBJECT ID will let you do this out of the box   Ex-  IF OBJECT ID  N db1 schema table1   N U   IS NOT NULL     SELECT 1 AS res ELSE SELECT 0 AS res    works just as well as  IF OBJECT ID  N db2 schema table1   N U   IS NOT NULL     SELECT 1 AS res ELSE SELECT 0 AS res    SQL SERVER 2016 Edit   Starting with 2016  Microsoft simplified the ability to check for non-existent objects prior to dropping  by adding the if exists keywords to drop statements   For example   drop table if exists mytablename   will do the same thing as OBJECT ID   INFORMATION SCHEMA wrappers  in 1 line of code   https   blogs msdn microsoft com sqlserverstorageengine 2015 11 03 drop-if-exists-new-thing-in-sql-server-2016

User · Answer

i taking here creating a view as example   Because ALTER CREATE commands can t be within BEGIN END blocks  You need to test for existence and the drop it before doing a create  IF Object ID  TestView   IS NOT NULL DROP VIEW TestView  GO  CREATE VIEW TestView    as           GO   If you are woried about the permissions being lost you can script the GRANT statements as well and re-run those at the end   You could wrap the create alter into a string and do an EXEC - that might get ugly for large 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

User · Answer

Using the Information Schema is the SQL Standard way to do it  so it should be used by all databases that support it

User · Answer

I ve had some problems either with selecting from INFORMATIONAL SCHEME and OBJECT ID  I don t know if it s an issue of ODBC driver or something   Queries from SQL management studio  both  were okay   Here is the solution   SELECT COUNT    FROM  lt yourTableNameHere gt    So  if the query fails  there is  probably  no such table in the database  or you don t have access permissions to it    The check is done by comparing the value  integer in my case  returned by SQL executor which deals with ODBC driver    if  sqlexec conectionHandle   SELECT COUNT    FROM myTable      -1         myTable doesn t exist

User · Answer

If anyone is trying to do this same thing in linq to sql  or especially linqpad  turn on option to include system tables and views and do this code   let oSchema   sys Schemas FirstOrDefault s  gt s Name  a schema   where oSchema   null let o oSchema  null sys Objects FirstOrDefault  o   gt  o Name  a item  amp  amp  o Schema id  oSchema Schema id  null where o  null   given that you have an object with the name in a property called item  and the schema in a property called schema where the source variable name is a

User · Answer

Looking for a table on a different database   if exists  select   from MyOtherDatabase sys tables where name    MyTable       print  Exists

User · Answer

For queries like this it is always best to use an INFORMATION SCHEMA view   These views are  mostly  standard across many different databases and rarely change from version to version   To check if a table exists use   IF  EXISTS  SELECT                     FROM INFORMATION SCHEMA TABLES                   WHERE TABLE SCHEMA    TheSchema                    AND  TABLE NAME    TheTable    BEGIN     --Do Stuff END

User · Answer

We always use the OBJECT ID style for as long as I remember  IF OBJECT ID   objectName     U   IS NOT NULL

User · Answer

You can use this         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

User · Answer

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

User · Answer

Please see the below approaches   Approach 1  Using INFORMATION SCHEMA TABLES view  We can write a query like below to check if a Customers Table exists in the current database   IF EXISTS  SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE NAME   N Customers   BEGIN     PRINT  Table Exists  END   Approach 2  Using OBJECT ID   function  We can use OBJECT ID   function like below to check if a Customers Table exists in the current database   IF OBJECT ID N dbo Customers   N U   IS NOT NULL BEGIN     PRINT  Table Exists  END   Approach 3  Using sys Objects Catalog View  We can use the Sys Objects catalog view to check the existence of the Table as shown below   IF EXISTS SELECT 1 FROM sys Objects WHERE  Object id   OBJECT ID N dbo Customers   AND Type   N U   BEGIN    PRINT  Table Exists  END   Approach 4  Using sys Tables Catalog View  We can use the Sys Tables catalog view to check the existence of the Table as shown below    IF EXISTS SELECT 1 FROM sys Tables WHERE  Name   N Customers  AND Type   N U    BEGIN       PRINT  Table Exists   END   Approach 5  Avoid Using sys sysobjects System table  We should avoid using sys sysobjects System Table directly  direct access to it will be deprecated in some future versions of the Sql Server  As per Microsoft BOL link  Microsoft is suggesting to use the catalog views sys objects sys tables instead of sys sysobjects system table directly     IF EXISTS SELECT name FROM sys sysobjects WHERE Name   N Customers  AND xtype   N U     BEGIN      PRINT  Table Exists    END   referred from  http   sqlhints com 2014 04 13 how-to-check-if-a-table-exists-in-sql-server

User · Answer

consider in one database you have a table t1  you want to run script on other Database like - if t1 exist then do nothing else create t1   To do this open visual studio and do the following   Right click on t1  then Script table as  then DROP and Create To  then New Query Editor   you will find your desired query  But before executing that script don t forget to comment out the drop statement in the query as you don t want to create new one if there is already one    Thanks

User · Answer

You can use below code  IF  OBJECT ID  TableName   IS NOT NULL   BEGIN   PRINT  Table Exists  END ELSE BEGIN    PRINT  Table NOT Exists  END   Or  IF  EXISTS  SELECT   FROM sys tables WHERE  name     TableName    BEGIN   PRINT  Table Exists  END ELSE BEGIN    PRINT  Table NOT Exists  END

User · Answer

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

User · Answer

I always check this way  IF OBJECT ID  TestXML  tblCustomer   IS NOT NULL   BEGIN       PRINT  Exist  END   ELSE BEGIN     PRINT  Not Exist  END   Thanks

User · Answer

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

User · Answer

-- -- create procedure to check if a table exists     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       -- -- how to use   check if table migrations exists      CALL checkIfTableExists  muDbName    migrations    output

[sql-server] Check if table exists in SQL Server

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005

Examples related to sql-server-2000