How to Query Table Names from a Specific Database in SQL (MySQL, SQL Server, Oracle)?

Retrieving Table Names from a Specific Database Using SQL

This guide demonstrates how to efficiently retrieve table names from a designated database within a multi-database environment (MySQL, SQL Server, Oracle). The standard query:

<code class="language-sql">SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'</code>

returns all table names across all databases. To filter for a specific database, database-specific syntax is necessary.

SQL Server:

Utilize the TABLE_CATALOG parameter:

<code class="language-sql">SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'</code>

Replace dbName with your database's name.

MySQL:

Employ the TABLE_SCHEMA parameter:

<code class="language-sql">SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName' </code>

Substitute dbName with your database name.

Oracle:

Oracle uses a different approach, leveraging the DBA_TABLES view:

<code class="language-sql">SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='dbName'</code>

Remember to replace dbName with your target database name. This query returns the table name and other relevant information. If you only need the table name, modify the SELECT statement accordingly.

The above is the detailed content of How to Query Table Names from a Specific Database in SQL (MySQL, SQL Server, Oracle)?. For more information, please follow other related articles on the PHP Chinese website!