6 Ways to Check if a Table Exists in SQL Server (T-SQL Examples)

This article offers five options for checking if a table exists in SQL Server. Most options involve querying a system view, but one of the options executes a system stored procedure, and another involves a function.

I also include some simple IF statements that can be modified to suit your circumstance.

Option 1 – The sys.tables View

This option queries the sys.tables system catalog view. This view returns a row for each user table. Therefore, you can query it using the table name you’re checking for.

Example:

SELECT 
  object_id 
FROM sys.tables
WHERE name = 'Artists';

Result:

+-------------+
| object_id   |
|-------------|
| 885578193   |
+-------------+

You can also add the schema name to the things you’re checking for. Here’s how to modify the previous query to include the schema name:

SELECT
  object_id  
FROM sys.tables
WHERE name = 'Artists'
AND SCHEMA_NAME(schema_id) = 'dbo';

Result:

+-------------+
| object_id   |
|-------------|
| 885578193   |
+-------------+

Note that the sys.tables view only returns the schema ID so I had to pass that to the SCHEMA_NAME() function in order to get its name. Alternatively I could have used the schema ID if I’d known that.

Example:

SELECT
  object_id  
FROM sys.tables
WHERE name = 'Artists'
AND schema_id = 1;

Result:

+-------------+
| object_id   |
|-------------|
| 885578193   |
+-------------+

Option 2 – The sp_tables Stored Procedure

The next option executes the sp_tables stored procedure.

Here’s how succinct your code can be when using this method:

sp_tables 'Artists'

Result:

+-------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|-------------------+---------------+--------------+--------------+-----------|
| Music             | dbo           | Artists      | TABLE        | NULL      |
+-------------------+---------------+--------------+--------------+-----------+

However, this stored procedure returns views as well as tables, so it’s a good idea to narrow it down to just tables (unless you’re also interested in having views returned). To narrow it down to just tables, use @table_type = "'TABLE'".

While you’re at it, you can specify the table owner and table qualifier too.

Example:

EXEC sp_tables 
  @table_name = 'Artists',  
  @table_owner = 'dbo',
  @table_qualifier = 'Music',
  @table_type = "'TABLE'",
  @fUsePattern = 1;

Result:

+-------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|-------------------+---------------+--------------+--------------+-----------|
| Music             | dbo           | Artists      | TABLE        | NULL      |
+-------------------+---------------+--------------+--------------+-----------+

It’s important to note that the @table_type parameter accepts a comma separated list. Therefore, it’s a bit different to the other parameters. The @table_type value must be enclosed in double quotes, and each item enclosed in single quotes. In my example, there’s only one list item, however, it still needs to be enclosed in both double, and single quotes.

Option 3 – INFORMATION_SCHEMA.TABLES

The INFORMATION_SCHEMA.TABLES system view returns one row for each table or view in the current database for which the current user has permissions. It’s similar to sys.tables, but it returns less columns. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Here’s an example of using it to check if a table exists in the current database:

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = 'Artists';

Result:

+-----------------+----------------+--------------+--------------+
| TABLE_CATALOG   | TABLE_SCHEMA   | TABLE_NAME   | TABLE_TYPE   |
|-----------------+----------------+--------------+--------------|
| Music           | dbo            | Artists      | BASE TABLE   |
+-----------------+----------------+--------------+--------------+

Here it is again, but this time I also specify the schema:

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = 'Artists'
AND TABLE_SCHEMA = 'dbo';

Result:

+-----------------+----------------+--------------+--------------+
| TABLE_CATALOG   | TABLE_SCHEMA   | TABLE_NAME   | TABLE_TYPE   |
|-----------------+----------------+--------------+--------------|
| Music           | dbo            | Artists      | BASE TABLE   |
+-----------------+----------------+--------------+--------------+

Option 4 – The OBJECT_ID() Function

You can also use a function such as OBJECT_ID() to see if it returns a non-NULL value.

Example:

SELECT OBJECT_ID('Artists', 'U') AS Result;

Result:

+-----------+
| Result    |
|-----------|
| 885578193 |
+-----------+

In this case, the table exists. Note that I used U to indicate the object type (user-defined table).

You can also provide a three part name to include the database and schema:

SELECT OBJECT_ID('Music.dbo.Artists', 'U') AS Result;

Result:

+-----------+
| Result    |
|-----------|
| 885578193 |
+-----------+

If the table doesn’t exist, you’ll get NULL:

SELECT OBJECT_ID('Oops!', 'U') AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

See below for an example of using this in an IF statement.

Option 5 – The sys.objects View

As if none of the previous examples will do the job, here’s yet another way to check if a table exists.

This time I query the sys.objects system catalog view. This view returns a row for each user-defined, schema-scoped object in the database. It doesn’t just return tables, it returns all sorts of objects. Therefore we need to narrow it down to just tables. In this case I’m only interested in user-defined tables, so I can use type = 'U' (U is for “USER_TABLE”). Alternatively, you could use TYPE_DESC = 'USER_TABLE'.

SELECT 
  object_id 
FROM sys.objects 
WHERE type = 'U' 
AND name = 'Artists';

Result:

+-------------+
| object_id   |
|-------------|
| 885578193   |
+-------------+

Here it is again, but specifying the schema:

SELECT 
  object_id  
FROM sys.objects 
WHERE type = 'U' 
AND name = 'Artists'
AND SCHEMA_NAME(schema_id) = 'dbo';

Result:

+-------------+
| object_id   |
|-------------|
| 885578193   |
+-------------+

Option 6 – The sys.sysobjects View (AVOID)

This option is only listed so that I can recommend against using it. The sys.sysobjects view is included in SQL Server for backwards compatibility, and Microsoft recommends that you avoid using this view in future work.

If you do encounter code that uses this view, consider modifying it to use sys.objects or some other system view or stored procedure.

In any case, here’s what the previous example might look like if using sys.sysobjects instead of sys.objects.

SELECT
  id  
FROM sys.sysobjects 
WHERE xtype = 'U' 
AND name = 'Artists';

Result:

+-----------+
| id        |
|-----------|
| 885578193 |
+-----------+

IF Statement 1

Here’s a simple IF statement that checks for the existence of the table, then prints a different message depending on the outcome. This code can be modified to suit your specific needs.

IF EXISTS 
  (SELECT object_id FROM sys.tables
  WHERE name = 'Artists'
  AND SCHEMA_NAME(schema_id) = 'dbo')
  PRINT 'The table exists'
ELSE 
  PRINT 'The table does not exist';

Result:

The table exists

And here’s what it looks like when the table doesn’t exist:

IF EXISTS 
  (SELECT object_id FROM sys.tables
  WHERE name = 'Customer'
  AND SCHEMA_NAME(schema_id) = 'dbo')
  PRINT 'The table exists'
ELSE 
  PRINT 'The table does not exist';

Result:

The table does not exist

IF Statement 2

Here’s another IF statement that can be modified to suit your specific needs.

IF OBJECT_ID('Artists', 'U') IS NOT NULL  
  PRINT 'The table exists'
ELSE 
  PRINT 'The table does not exist';

Result:

The table exists