When it comes to looking at a table and its column definitions, there are two data dictionary views you need to be concerned with. These views are:
The ALL_TABLES view contains one row for each table. You can use this view to get a quick list of tables you own or to which you have been granted some type of access. ALL_TABLES has a one-to-many relationship to ALL_TAB_COLUMNS, which contains one record for each column in a table. ALL_TAB_COLUMNS is the source for information on column names, datatypes, default values, and so forth.
Listing Tables You Own
To get a quick list of tables you own, it’s easier to use the USER_TABLES view than ALL_TABLES. Remember, USER_TABLES shows you only the tables you own. To see a list of your tables, simply select the table_name column.
SELECT table_name FROM user_tables;TABLE_NAME ------------------------------ DATA_TYPE_TEST DT_TEST EMPLOYEE PROJECT PROJECT_HOURS 5 rows selected.
To see tables owned by other users, you need to query the ALL_TABLES view. Just be sure to qualify your query by specifying the owner’s username in the WHERE clause. Here’s an example that lists all tables owned by the user SYSTEM:
SELECT table_name FROM all_tables WHERE owner = 'SYSTEM';
You can make things easy on yourself by writing a script to list tables. The following listing shows one approach you can take. It prompts you for a username, then lists all tables owned by that user. The table listing is in alphabetical order.