Check if Table Exists in SQL Server

To check if a table exists in SQL Server, you can use the system views or metadata functions provided by SQL Server. Below are the most common methods to perform this check:

Method 1: Using INFORMATION_SCHEMA.TABLES

The INFORMATION_SCHEMA.TABLES view provides information about all the tables in a database.

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'schema_name' 
           AND TABLE_NAME = 'table_name')
BEGIN
    PRINT 'Table exists.'
END
ELSE
BEGIN
    PRINT 'Table does not exist.'
END

• Replace schema_name with the schema of the table (e.g., dbo).
• Replace table_name with the name of the table.

Method 2: Using sys.objects

The sys.objects catalog view contains information about all schema-scoped objects.

IF EXISTS (SELECT 1 
           FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'schema_name.table_name') 
           AND type = 'U')
BEGIN
    PRINT 'Table exists.'
END
ELSE
BEGIN
    PRINT 'Table does not exist.'
END

• OBJECT_ID gets the unique identifier of the table.
• type = 'U' specifies that you are looking for user-defined tables.

Method 3: Using OBJECT_ID Directly

You can use the OBJECT_ID function directly to check if the table exists.

IF OBJECT_ID('schema_name.table_name', 'U') IS NOT NULL
BEGIN
    PRINT 'Table exists.'
END
ELSE
BEGIN
    PRINT 'Table does not exist.'
END

• 'U' specifies that the object is a user table.
• Replace schema_name.table_name with the fully qualified name of your table.

Method 4: Using sys.tables

The sys.tables view specifically stores information about all user tables.

IF EXISTS (SELECT 1 
           FROM sys.tables 
           WHERE name = 'table_name' 
           AND SCHEMA_NAME(schema_id) = 'schema_name')
BEGIN
    PRINT 'Table exists.'
END
ELSE
BEGIN
    PRINT 'Table does not exist.'
END

Example

Suppose you want to check if a table named Employees exists in the dbo schema:

Using INFORMATION_SCHEMA.TABLES

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'dbo' 
           AND TABLE_NAME = 'Employees')
BEGIN
    PRINT 'Employees table exists.'
END
ELSE
BEGIN
    PRINT 'Employees table does not exist.'
END

Using OBJECT_ID

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
BEGIN
    PRINT 'Employees table exists.'
END
ELSE
BEGIN
    PRINT 'Employees table does not exist.'
END

Key Notes

1. Replace schema_name with the schema name (dbo is the default schema).
2. Replace table_name with the name of your table.
3. Use these methods in any SQL Server Management Studio (SSMS) query window.