Problem
You want to see a list all the tables you've created in a given schema.
Solution
The solutions that follow all assume you are working with the SMEAGOL schema. The basic approach to a solution is the same for all RDBMSs: you query a system table (or view) containing a row for each table in the database.
DB2
Query SYSCAT.TABLES:
1 select tabname 2 from syscat.tables 3 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:
1 select table_name 2 from information_schema.tables 3 where table_schema = 'SMEAGOL'
Discussion
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.
|
Oracle's system views and DB2's system tables are each vendor-specific. PostgreSQL, MySQL, and SQL Server, on the other hand, support something called the information schema, which is a set of views defined by the ISO SQL standard. That's why the same query can work for all three of those databases.