4.2 How do I…Determine all of the tables owned by a particular user?

Problem

I need an easy way to determine the names of all the tables owned by a user account when creating my application. I have access to a large number of tables and I cannot always remember their names. How can I determine all of the tables owned by a particular user?

Technique

The tables available to a user account can be queried from the data dictionary. The USER_TABLES data dictionary view contains all tables owned by the user account connected to the database. The ALL_TABLES view contains all tables for which the connected user account has privileges. By querying this view for a particular owner, you can determine that owner’s tables. Figure 4.3 shows most of the columns in the ALL_TABLES data dictionary view. For a full explanation of the ALL_TABLES view, see How-To 4.6.

The OWNER column contains the name of the user account whose schema contains the table. The TABLE_NAME column contains the name of the table. When a table from another user account’s schema is referenced, both the owner and table name must be provided unless a synonym is used.

Steps

1. Connect to SQL*Plus as the WAITE user account. You can also use any account with CREATE TABLE privileges. Load CHP4_2.SQL in the SQL buffer. The query contained in the file returns all tables owned by the connected user account.

SQL> GET CHP4_2.sql

1 SELECT TABLE_NAME

2 FROM USER_TABLES

3* ORDER BY TABLE_NAME

SQL>

Line 1 contains the TABLE_NAME column in the select list of the query. Line 2 specifies the USER_TABLES data dictionary view as the source of the query. The ORDER BY clause in line 3 returns the results ordered by TABLE_NAME.

2. Execute the query to view the tables owned by the WAITE user account. The tables shown as the results of the query were created by CHP1_1.SQL in Chapter 1 and CHP4_1.SQL in Chapter 4. Your results may vary depending on the tables you have created for the WAITE user account.

SQL> /

TABLE_NAME

------------------------------

DEPT01

EMP01

EMPLOYEE04

3. Load CHP4_3.SQL into the SQL buffer. The file contains a query of the ALL_TABLES data dictionary view to return all tables that the connected user account owns or has privileges to use. The OWNER column is included in the select list in order to determine which user account owns the table.

SQL> GET CHP4_3.sql

1 SELECT OWNER, TABLE_NAME

2 FROM ALL_TABLES

3* ORDER BY OWNER, TABLE_NAME

Line 1 contains the select list of the query. The OWNER and TABLE_NAME columns are returned by the query. Line 2 specifies the ALL_TABLES data dictionary view as the source of the query. The ORDER BY clause contained in line 3 orders the output by OWNER and TABLE_NAME.

4. Execute the query. Because the WAITE user account was granted the DBA role by the installation script, every table in the database will be included in the query. The output of the query has been abbreviated in order to conserve space.

SQL> /

OWNERTABLE_NAME
------------------------------ ------------------------------
SYS TRIGGER$
SYSUET$
SYSUNDO$
SYS USER$
SYS VIEW$
WAITE DEPT01
WAITE EMPLOYEE04
7 rows selected

How It Works

The USER_TABLES and ALL_TABLES data dictionary views contain information about the tables to which a user account has access. The USER_TABLES view contains information about the tables in the user account’s schema. The ALL_TABLES view contains all tables on which the user account has privileges. Steps 1 and 2 query the TABLE_NAME column from the USER_TABLES view to show all tables owned by the current user account. Steps 3 and 4 show all tables the user account owns in addition to the tables to which it has been granted privileges. The ALL_TABLES view is queried in steps 3 and 4.

Comments

If you own all of the tables you work with, the USER_TABLES view is the fastest way to determine the names of the tables. The USER_TABLES view only contains tables owned by the connected user account.