Portable SQL/Statements/Access metadata/Tables

ANSI

SQL-92 defines a collection of objects in INFORMATION_SCHEMA which contains the list of available tables:

SELECT table_name
FROM information_schema.tables
WHERE
 table_type = 'BASE TABLE' AND
 table_schema = dbname

Please replace dbname with the database name you want to get the table names from because the information schema contains all tables from all databases which are managed by the DBMS.

Firebird/Interbase

SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE
 RDB$SYSTEM_FLAG=0 AND
 RDB$VIEW_BLR IS NULL

MS SQL Server

SELECT TableName
FROM information_schema.tables
WHERE
 table_type = 'BASE TABLE' AND
 OBJECTPROPERTY(OBJECT_ID(TableName), 'IsMsShipped') = 0
ORDER BY TableName

MySQL

Fully compatible with SQL-92.

Oracle

SELECT table_name
FROM user_tables

PostgreSQL

SELECT table_name
FROM information_schema.tables
WHERE
 table_type = 'BASE TABLE' AND
 table_schema NOT IN ('pg_catalog', 'information_schema')

SQLite

Doesn't support retrieving tables via an SQL statement.

Sybase ASE

EXEC sp_tables @table_type = "'TABLE'"