SQL: Check if table exists

On By Ben Larson Ph.D.In MS SQL Server

Copy, paste and run this code in your SQL Server Management Studio to build the database and table we are using in this exercise:

create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );

To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA.TABLES table.

Running the following code, produces the results below:

USE SANDBOX;

Select *
from INFORMATION_SCHEMA.TABLES;

1

You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_id')
BEGIN
  PRINT 'Yes'
END

2

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_ids')
BEGIN
  PRINT 'Yes'
END

ELSE

BEGIN
  PRINT 'No'
End

3

One of the more common uses I find for this when I need to create a table in a script. I want to make sure a table with same name doesn’t already exist, or my query will fail. So I write a query like the one below.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_id')
BEGIN
  drop table employee_id
END

ELSE

BEGIN
  print 'Table does not exist'
End
 CREATE TABLE employee_id (
        emp_nm nvarchar(30) not null,
             emp_id nvarchar(8),
             b_emp_id nvarchar(8)
             PRIMARY KEY(emp_nm) );

INSERT INTO employee_id
       (emp_nm, emp_id)
VALUES
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568')

INSERT INTO employee_id
       (emp_nm, b_emp_id)
VALUES
       ('Priyanka', 'B1234567');

5 thoughts on “SQL: Check if table exists”

  1. This may sound stupid, but I would like to know if there is a way to verify if a list of tables exists before doing an action. If I have 12 tables to verify, do I have to repeat “If exists bla bla bla” 12 times?
    I tried doing …
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = N’employee_id’
    and TABLE_NAME = N’employee_address’
    and TABLE_NAME = N’employee_division’ )

    But it not working. Any idea?

    Reply
    1. Sophia Hawes-Tingey

      You can and the EXISTS clauses together:

      IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = N’employee_id’)
      AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = N’employee_address’)
      AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N’employee_division’)

      Reply
    2. Profex

      FYI, logically using AND doesn’t make sense in the same WHERE clause…I don’t know of anyone who’s first name is “Mary” AND “Joe” at the same time. It’s either one or the other, but it can’t be both, therefore it will always return FALSE; even if it’s “MaryJoe” 🙂

      Sophia’s method works…

      Reply
  2. Anonymous

    No its wrong
    actual ans will be
    FOR TABLE USE
    IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N’Schemaname.Tablename’) and type in(N’U’))
    and
    for procedure and views
    if OBJECT_ID(View/Procedure)IS NOT NULL
    DROP View/Procedure
    .
    .
    .
    .
    .
    ETC

    Reply
  3. max

    SP2-0734: unknown command beginning “IF NOT EXI…” – rest of line ignored.
    drop table bkp
    *
    ERROR at line 2:
    ORA-06550: line 2, column 3:
    PLS-00103: Encountered the symbol “DROP” when expecting one of the following:
    ( begin case declare exit for goto if loop mod null pragma
    raise return select update while with
    <<
    continue close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall merge pipe purge

    INCORRECT

    Reply

Leave a Reply to mylenechalutCancel reply