Querying Database Table Names with SQL

Retrieving Table Names in SQL Databases

When working with relational databases, it’s often necessary to programmatically retrieve a list of tables within a specific database. This is common in tasks like database documentation, data migration, or building dynamic application interfaces. SQL provides several ways to achieve this, with slight variations depending on the database system (MySQL, SQL Server, Oracle, etc.). This tutorial will cover the most common and portable approaches.

The INFORMATION_SCHEMA

Most SQL database systems adhere to the SQL standard and provide a metadata database called INFORMATION_SCHEMA. This schema contains views and tables that describe the structure of the database itself – tables, columns, data types, constraints, and more. We can query INFORMATION_SCHEMA.TABLES to get a list of tables.

Here’s the basic query:

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

This query retrieves the TABLE_NAME column from the INFORMATION_SCHEMA.TABLES view, filtering for rows where TABLE_TYPE is ‘BASE TABLE’. This ensures we only get regular tables and exclude views, system tables, and other non-table objects.

Important: This query will list tables from all databases accessible to your current user. To restrict the query to a specific database, we need to add a filtering condition based on the database name.

Specifying the Database

The method for specifying the database name varies slightly between database systems.

MySQL:

In MySQL, you use the TABLE_SCHEMA column to specify the database name:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_TYPE = 'BASE TABLE';

Replace 'your_database_name' with the actual name of the database you want to query.

SQL Server:

In SQL Server, you use the TABLE_CATALOG column:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'your_database_name' AND TABLE_TYPE = 'BASE TABLE';

Replace 'your_database_name' with the actual database name.

Oracle:

Oracle uses different system views for metadata. Instead of INFORMATION_SCHEMA, you would query DBA_TABLES (or USER_TABLES for tables owned by the current user):

SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = 'your_schema_name'; -- Schemas are like databases in Oracle

Replace 'your_schema_name' with the schema (database) name.

Direct Database Context (SQL Server & MySQL)

An alternative, often simpler, approach is to explicitly set the database context before querying.

SQL Server:

USE your_database_name;
SELECT *
FROM sys.Tables;

MySQL:

USE your_database_name;
SHOW TABLES;

This approach first tells the database server to operate within the specified database, after which you can directly query system views like sys.Tables (SQL Server) or use the SHOW TABLES command (MySQL) to retrieve the list of tables. This simplifies the query but requires you to change the database context before executing the query.

Choosing the Right Approach

• Portability: Using INFORMATION_SCHEMA with the appropriate database name filter is the most portable approach, as it generally adheres to the SQL standard.
• Simplicity: Setting the database context using USE (SQL Server & MySQL) or equivalent is often the simplest option, but it’s less portable and may require code changes if you’re working with multiple database systems.
• Performance: For very large databases, querying INFORMATION_SCHEMA might be slower than directly querying system tables or using database-specific commands. In such cases, consider the performance implications when choosing your approach.