Check if a Table Exists in a Given Schema in SQL

1. Introduction

In database management, it’s a common task to verify the existence of a table in a given schema. This verification is crucial for performing operations such as generating reports dynamically based on existing tables or seamlessly integrating new application features that depend on specific database structures.

In this tutorial, we’ll explore several approaches to achieving this task in PostgreSQL 16, MySQL 8, and SQL Server 2022. However, the methods we discuss should work in other versions and database systems.

2. Understanding the Problem

In an SQL database, a schema is the logical container of database objects such as tables, views, procedures, etc. Schemas help organize and manage these objects, providing a way to group related entities and control access.

For this tutorial, we’ll use our University database. All the tables are created in the default schema named public in PostgreSQL and dbo in SQL Server. In MySQL, the term schema is synonymous with database.

3. Using Information Schema

The Information Schema is an ANSI SQL-based metadata schema that stores information about all tables and other database objects. It can be used in all ANSI-compliant databases to retrieve the list of tables within a schema.

3.1. PostgreSQL

The information_schema.tables view stores a list of tables and some basic information about each. A combination of EXISTS and a subquery can check the existence of a table in the particular schema:

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

The above query checks if a table named student is in the public schema. We use the EXISTS condition to return a boolean result based on the output of the SELECT query.

However, it’s important to note that while querying from the information_schema, PostgreSQL returns only the tables the current user can access. Therefore, if a user with limited access runs this query, it might return false even if the table exists in the database.

In PostgreSQL, queries are generally case-sensitive. Therefore, it’s essential to ensure that table and schema names in the query have the correct casing.

3.2. SQL Server

Let’s look at the corresponding query in SQL Server using the information schema:

IF EXISTS (
  SELECT 1 
  FROM information_schema.tables 
  WHERE table_schema='dbo' 
  AND table_name='student'
) SELECT 1 ELSE SELECT 0;

In this query, we use the IF EXISTS condition in SQL Server to check if the specified table exists in the schema. If the subquery returns a result, it returns 1; otherwise, it returns 0.

Like in PostgreSQL, the results obtained from information_schema in SQL Server depend on the user’s permissions. However, unlike PostgreSQL, SQL Server uses case-insensitive queries by default.

3.3. MySQL

As mentioned before, in MySQL Server, the term schema is synonymous with the database. Let’s write the query to check the existence of the table:

SELECT EXISTS (
  SELECT 1
  FROM information_schema.tables
  WHERE table_schema = 'University'
  AND table_name = 'Student'
) as table_exist;

MySQL adheres to the ANSI Standard by storing the database name in the table_schema column within information_schema.tables. Similar to PostgreSQL and SQL Server, the outcome of the above query depends on user permissions.

For regular data, MySQL treats queries as case-insensitive by default. However, for information_schema, case sensitivity depends on the operating system. On Linux/Unix, MySQL is case-sensitive by default, whereas on Windows, it is case-insensitive by default.

4. Using Database Specific Approach

Most databases offer alternative methods to retrieve metadata about database objects besides using the information schema.

4.1. PostgreSQL

PostgreSQL stores the metadata of the tables in pg_tables. We can query from this table to check for the existence of the required table:

SELECT EXISTS (
  SELECT 1
  FROM pg_tables
  WHERE schemaname = 'public'
  AND tablename = 'student'
);

This query operates similarly to one using information_schema. However, the pg_tables query doesn’t filter results based on the user’s permissions. Thus, if a user has access to pg_tables, they can list all tables in the database without restrictions on individual table access.

An alternate way to perform this is to use the to_regclass() function. Let’s look at this approach:

SELECT COALESCE(to_regclass('public.student') IS NOT NULL, FALSE)

The to_regclass() function checks if a database object with the specified name exists. If it does, it returns the object ID; otherwise, it returns NULL. We can use the COALESCE() function to convert this result into a boolean value. However, this approach returns true if any database object with that name exists in the schema, not just a table.

4.2. SQL Server

SQL Server stores the metadata in a table named sys.tables, which we can query as follows:

IF EXISTS ( 
  SELECT 1 FROM sys.tables 
  WHERE SCHEMA_NAME(schema_id) = 'dbo' 
  AND NAME='student' 
) SELECT 1 ELSE SELECT 0;

In contrast to PostgreSQL, SQL Server’s sys.tables doesn’t directly store the schema name. Instead, it references schemas using a unique identifier called schema_id. To retrieve the schema’s name based on its identifier, we use the SCHEMA_NAME() function.

Like pg_tables in PostgreSQL, sys.tables in SQL Server is available to all users regardless of their permissions to specific tables.

5. Conclusion

In this tutorial, we explored various methods of verifying the existence of a table within a schema. While the information_schema provides a standardized approach across different databases, it’s restricted by the user’s permissions on specific tables. Consequently, a user can only check the existence of the tables to which they have explicit access. This limitation helps maintain security but may result in incomplete metadata retrieval.

PostgreSQL and SQL Server provide alternative methods that don’t depend on user permissions.