How to Retrieve Table Names from a Specific Database Using SQL?

Efficiently Retrieving Table Names from Specific Databases with SQL

Managing multiple database servers (e.g., MySQL, MS SQL Server) often requires extracting specific database information. A frequent task is obtaining a list of table names from a designated database.

While the generic query:

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

retrieves all tables across all databases on a server, it's often necessary to limit results to a single database. The precise SQL syntax for this varies across database systems.

Database-Specific Queries:

The following examples demonstrate how to retrieve table names from a specific database:

SQL Server:

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

Remember to substitute "dbName" with your target database's name.

MySQL:

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

Replace "dbName" with the desired database name.

Oracle:

Oracle uses a slightly different approach:

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

Here, "dbName" represents the database user who owns the tables. Note that this query requires appropriate privileges.

The above is the detailed content of How to Retrieve Table Names from a Specific Database Using SQL?. For more information, please follow other related articles on the PHP Chinese website!