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