How to Check If a Table Exists in a Given Schema

How to check if a table exists in a given schema

It depends on what you want to test exactly.

Information schema?

To find "whether the table exists" (no matter who's asking), querying the information schema (information_schema.tables) is incorrect, strictly speaking, because (per documentation):

Only those tables and views are shown that the current user has access
to (by way of being the owner or having some privilege).

The query provided by @kong can return FALSE, but the table can still exist. It answers the question:

How to check whether a table (or view) exists, and the current user has access to it?

SELECT EXISTS (
   SELECT FROM information_schema.tables 
   WHERE  table_schema = 'schema_name'
   AND    table_name   = 'table_name'
   );

The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (information_schema.tables is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actually carry all information.

System catalogs

Your question was:

How to check whether a table exists?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   AND    c.relkind = 'r'    -- only tables
   );

Use the system catalogs pg_class and pg_namespace directly, which is also considerably faster. However, per documentation on pg_class:

The catalog pg_class catalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
see also pg_index), sequences, views, materialized views, composite
types, and TOAST tables;

For this particular question you can also use the system view pg_tables. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):

SELECT EXISTS (
   SELECT FROM pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename  = 'table_name'
   );

Identifiers have to be unique among all objects mentioned above. If you want to ask:

How to check whether a name for a table or similar object in a given schema is taken?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );

• Related answer on dba.SE discussing "Information schema vs. system catalogs"

Alternative: cast to regclass

SELECT 'schema_name.table_name'::regclass

This raises an exception if the (optionally schema-qualified) table (or other object occupying that name) does not exist.

If you do not schema-qualify the table name, a cast to regclass defaults to the search_path and returns the OID for the first table found - or an exception if the table is in none of the listed schemas. Note that the system schemas pg_catalog and pg_temp (the schema for temporary objects of the current session) are automatically part of the search_path.

You can use that and catch a possible exception in a function. Example:

• Check if sequence exists in Postgres (plpgsql)

A query like above avoids possible exceptions and is therefore slightly faster.

to_regclass(rel_name) in Postgres 9.4+

Much simpler now:

SELECT to_regclass('schema_name.table_name');

Same as the cast, but it returns ...

... null rather than throwing an error if the name is not found

T-SQL Check if table exists in schema

You could use the schemas object as well. For example:

IF EXISTS (SELECT 1
           FROM sys.tables t
                JOIN sys.schemas s ON t.schema_id = s.schema_id
           WHERE s.[name] = N'schema_name'
             AND t.name = N'table_name'
             AND t.type = 'U')
    DROP TABLE [schema_name].[table_name];
GO

Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

How do I check if a table exists?

You can check below code;

SELECT EXISTS (
   SELECT 1
   FROM   information_schema.tables 
   WHERE  table_schema = 'schema_name'
   AND    table_name = 'table_name'
   );

or

SELECT EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );

or

SELECT 'schema_name.table_name'::regclass

or

SELECT to_regclass('schema_name.table_name');

If not exist this query returns null

Check if table exists in default schema for a user?

Try the following, after substituting TheTable with the actual table name you are trying to locate:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = SCHEMA_NAME() 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff here
END

SCHEMA_NAME() will return the name of the default schema of the caller. [ Source ]

Can I write a program to see if a table exists in YugaByte's YCQL (Cassandra) api?

Yes, you can do the same for YugaByte DB's YCQL. Here's an example that shows how to check for the existence of a keyspace and that of a table via cqlsh.

Setup:

cqlsh> CREATE KEYSPACE IF NOT EXISTS ksp;

cqlsh> CREATE TABLE IF NOT EXISTS ksp.t(k int PRIMARY KEY, v int);

To check if a keyspace exists

cqlsh> select count(*) from system_schema.keyspaces 
       where keyspace_name = 'ksp';


 count
-------
     1

(1 rows)
cqlsh> select count(*) from system_schema.keyspaces 
       where keyspace_name = 'non-existent-ksp';

 count
-------
     0

(1 rows)

To check if a table exists

cqlsh> select count(*) from system_schema.tables 
       where keyspace_name = 'ksp' and table_name = 't';

 count
-------
     1

(1 rows)
cqlsh> select count(*) from system_schema.tables 
        where keyspace_name = 'ksp' and table_name = 'non-existent-t';

 count
-------
     0

(1 rows)

Check if table exists in c#

You cannot do this in a cross-database way. Generally DDL (that is, the code for creating tables, indexes and so on) is completely different from database to database and so the logic for checking whether tables exist is also different.

I would say the simplest answer, though, would simply be something like:

SELECT * FROM <table> WHERE 1 = 0

If that query gives an error, then the table doesn't exist. If it works (though it'll return 0 rows) then the table exists.

Be very careful with what you let the user input, though. What's to stop him from from specifying "sysusers" as the table name (in SQL Server, that'll be the list of all database users)

How to check a condition whether table exist or not in RMysql

Here's what I do. Maybe there's something else that's more robust or generalizable?

Just "show tables" as a query and check for the presence of your table name in the result.

loadData <- function() {
  db <- dbConnect(
    MySQL(),
    dbname = databaseName,
    host = host,
    port = port,
    user = user,
    password = password
  )

  rs <- dbSendQuery(con, "show tables")
  table.frame <- fetch(rs, n = -1)
  if ("some_table" %in% table.frame[, 1]) {

    res <- dbSendQuery(db, "SELECT * FROM some_table")
    final_data <- dbFetch(res)
    dbDisconnect(db)
    return(final_data)

  } else {
    return(NULL)
  }
}

---

Related Topics