Metadata Queries: Listing Tables in a Schema
DB2
Query SYSCAT.TABLES:
select tabname from syscat.tables where tabschema = 'SMEAGOL'
Oracle
Query SYS.ALL_TABLES:
select table_name from all_tables where owner = 'SMEAGOL'
PostgreSQL, MySQL, and SQL Server
Query INFORMATION_SCHEMA.TABLES:
select table_name from information_schema.tables where table_schema = 'SMEAGOL'
In a delightfully circular manner, databases expose information about themselves through the very mechanisms that you create for your own applications: tables and views. Oracle, for example, maintains an extensive catalog of system views, such as ALL_TABLES, that you can query for information about tables, indexes, grants, and any other database object.
From SQL Cookbook
By Anthony Molinaro
posted by kanun # 8:20 PM 
