SQL: Check if table exists

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 
GO;

Select *
from INFORMATION_SCHEMA.TABLES
GO;

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 = N'employee_id')
BEGIN
  PRINT 'Yes'
END

2

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = N'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 = N'employee_id')
BEGIN
  drop table employee_id
END

ELSE

BEGIN
 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');
End
Advertisements

4 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?

    Like

    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’)

      Like

    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…

      Like

  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

    Like

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s