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:

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

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:

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

Replace dbName with your database's name.

MySQL:

Employ the TABLE_SCHEMA parameter:

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

Substitute dbName with your database name.

Oracle:

Oracle uses a different approach, leveraging the DBA_TABLES view:

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='dbName'

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!