List SQL Tables by Engine: Schemas, Views, and Permissions

List tables in the right database, schema, and client

There is no one table-listing command that works in every database and client. Start by identifying the engine, connected database or catalog, schema, and current role. Metadata visibility is permission-sensitive: an empty result does not necessarily prove that no objects exist. This reference separates SQL queries from client commands and tables from views.

PostgreSQL: SQL metadata and psql commands differ

This SQL query lists visible base tables in the public schema of the current PostgreSQL database. Change the schema value deliberately; it is a value filter, not a database name. information_schema.tables can also contain views and other relation types, so the table_type condition matters.

SELECT table_catalog,table_schema,table_name
FROM information_schema.tables
WHERE table_schema='public' AND table_type='BASE TABLE'
ORDER BY table_schema,table_name;

In the psql command-line client, use the following client meta-command. It is not SQL and should not be pasted into a generic application SQL call:

\dt public.*

The output depends on the connected database, privileges, and existing objects. To verify how temporary objects are classified without relying on an application schema, run this disposable PostgreSQL 16 fixture:

BEGIN;
CREATE TEMP TABLE listing_demo(id integer) ON COMMIT DROP;
CREATE TEMP VIEW listing_demo_view AS SELECT id FROM pg_temp.listing_demo;
SELECT table_name,table_type
FROM information_schema.tables
WHERE table_schema=pg_my_temp_schema()::regnamespace::text
 AND table_name IN ('listing_demo','listing_demo_view')
ORDER BY table_name;
ROLLBACK;

table_name | table_type
listing_demo | LOCAL TEMPORARY
listing_demo_view | LOCAL TEMPORARY

Both the temporary table and temporary view use LOCAL TEMPORARY in this information-schema view. A BASE TABLE filter intentionally omits both. For temporary relation kinds, inspect the PostgreSQL catalog rather than assuming table_type distinguishes them. Materialized views are another object category and require the appropriate PostgreSQL catalog view. See information_schema.tables and the psql reference.

MySQL 8.4: request the object type

SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';

This uses the currently selected database. SHOW FULL TABLES can identify views as well as base tables; the filter requests base tables only. Inspect your selected database before interpreting the list. See SHOW TABLES.

SQL Server: include the schema in the result

SELECT s.name AS schema_name,t.name AS table_name
FROM sys.tables AS t JOIN sys.schemas AS s ON s.schema_id=t.schema_id
WHERE t.is_ms_shipped=0
ORDER BY s.name,t.name;

This lists visible user tables in the current database. The same table name can occur in several schemas, so retaining schema_name prevents ambiguity. Metadata visibility depends on ownership and permissions. See sys.tables.

Oracle: accessible tables versus owned tables

SELECT owner,table_name FROM all_tables ORDER BY owner,table_name;

ALL_TABLES describes tables accessible to the current user; USER_TABLES describes owned tables. It does not mean every table in every database regardless of access. Views have their own metadata views. See Oracle ALL_TABLES.

Snowflake: a database qualifier is not a schema filter

SELECT table_catalog,table_schema,table_name
FROM MY_DATABASE.INFORMATION_SCHEMA.TABLES
WHERE table_schema='PUBLIC' AND table_type='BASE TABLE'
ORDER BY table_schema,table_name;

Replace MY_DATABASE with the intended database identifier and PUBLIC with the exact schema name. The database qualifies INFORMATION_SCHEMA; table_schema filters a schema inside it. Object visibility follows the active role. See Snowflake’s TABLES view.

Databricks: its SHOW pattern is not SQL LIKE syntax

SHOW TABLES IN main.default LIKE 'sales*';

This example targets a Unity Catalog catalog/schema named main.default; replace it with an existing namespace. In this command’s pattern language, * matches multiple characters; do not substitute SQL LIKE’s percent sign by assumption. The result also reports whether an object is temporary. See Databricks SHOW TABLES.

Hive: check the selected namespace and object kind

SHOW TABLES;

This enumerates names in the current database under Hive’s SHOW TABLES behavior; use the appropriate DESCRIBE command or catalog metadata to inspect a selected object. Do not infer its full structure, storage, or permissions from its name alone. See the Hive DDL reference.

Interpret scope before counting objects

The PostgreSQL fixture and its displayed output were executed. The other commands are documentation-checked references, not claims of execution against those services. Their actual names and row counts depend on your environment. Verify the selected database/catalog, schema, role, and object-type filter before deciding that a table is missing.

FAQ

Why can a colleague see more tables? They may use another database, schema, or role. Why does a GUI reject backslash-dt? It is a psql command, not server SQL. Does listing a table authorize reading its data? Metadata visibility and data access are distinct privileges.