Difference between revisions of "Portable SQL/Statements/Access metadata/Tables"

Revision as of 11:30, 7 May 2008

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_schema = dbname AND
 table_type = 'BASE TABLE'

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'"