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:
| ALL_TABLES |
| ALL_TAB_COLUMNS |
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.
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.
SQL> 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.
--DESCRIPTION
--List tables owned by the current user, or owned by a specified user.
--The user is prompted for an owner name. If an owner is not specified,
--i.e. the user just presses ENTER, then tables are listed for
--the current user.
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET DEFINE ON
SET HEADING OFF
--Ask the user for the owner.
ACCEPT username CHAR PROMPT 'List tables for user: '
PROMPT
--Set up the title to print the owner.
TTITLE LEFT 'TABLES OWNED BY: ' owner SKIP 1
COLUMN owner NOPRINT NEW_VALUE owner
--List the tables.
SELECT DECODE('&&username',NULL,USER,UPPER('&&username')) owner,
' ' indent,
table_name
FROM all_tables
WHERE owner = DECODE('&&username',NULL,USER,UPPER('&&username'))
ORDER BY table_name;
--Clean up: undefine vars, clear columns, change settings back to defaults.
UNDEFINE username
UNDEFINE owner
COLUMN owner CLEAR
TTITLE OFF
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ONWhen you run this script, it will prompt you for a username. Then it will list all the tables owned by that user. Here’s an example:
SQL>@list_tablesList tables for user:jeffTABLES OWNED BY: jeff EMPLOYEE MY_TABLE PROJECT PROJECT_HOURS
As you can see, the resulting list shows all the tables owned by the user named JEFF. The DECODE function in the SELECT statement translates any username you enter to uppercase. If you were to press ENTER at the username prompt, that same DECODE statement would translate your empty response to your username, so you would see your tables.
To list the columns in a table, you will almost certainly want to write a script. The script shown next offers one possible approach to doing this. Some of the expressions, particularly the ones that call the DECODE function, get a bit complicated. I’ll explain those after the listing.
--DESCRIPTION
--List all the columns for a table, together with
--their datatypes, default values, and nullability.
--
--INPUTS
-- Param 1 A table name, optionally qualified
-- optionally qualified by an owner name.
-- For example: "SYS.ALL_TAB_COLUMNS".
--
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET RECSEP OFF
SET HEADING ON
SET PAGESIZE 9999
SET LONG 1000
SET LINESIZE 80
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
BTITLE OFF
--Dissect the input argument, and get the owner name and
--table name into two seperate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
DECODE(INSTR('&&1','.'),
0,USER, /*Default to current user.*/
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
DECODE(INSTR('&&1','.'),
0,UPPER('&&1'), /*Only the table name was passed in.*/
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
FROM dual;
--Set up the page title, force just one page break by breaking on
--the table name column.
TTITLE LEFT -
"COLUMN LISTING FOR " s_owner_name '.' s_table_name -
SKIP 2
BREAK ON table_name SKIP PAGE
--Set up the formats and widths for the columns to be displayed.
COLUMN column_id NOPRINT
COLUMN table_name NOPRINT
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN data_type FORMAT A17 HEADING 'Data Type'
COLUMN not_null FORMAT A9 HEADING 'Nullable?'
SELECT table_name,
column_id,
column_name,
DECODE (data_type,
'VARCHAR2','VARCHAR2 (' || TO_CHAR(data_length) || ')',
'NVARCHAR2','NVARCHAR2 (' || TO_CHAR(data_length) || ')',
'CHAR','CHAR (' || TO_CHAR(data_length) || ')',
'NCHAR','NCHAR (' || TO_CHAR(data_length) || ')',
'NUMBER',
DECODE (data_precision,
NULL, 'NUMBER',
'NUMBER (' || TO_CHAR(data_precision)
|| ',' || TO_CHAR(data_scale) || ')'),
'FLOAT',
DECODE (data_precision,
NULL, 'FLOAT',
'FLOAT (' || TO_CHAR(data_precision) || ')'),
'DATE','DATE',
'LONG','LONG',
'LONG RAW','LONG RAW',
'RAW','RAW (' || TO_CHAR(data_length) || ')',
'MLSLABEL','MLSLABEL',
'ROWID','ROWID',
'CLOB','CLOB',
'NCLOB','NCLOB',
'BLOB','BLOB',
'BFILE','BFILE',
data_type || ' ???') data_type,
DECODE (nullable, 'N','NOT NULL') not_null
FROM all_tab_columns
WHERE table_name = '&&s_table_name'
AND owner = '&&s_owner_name'
ORDER BY column_id;
--Now list any columns with default values defined.
--This is done as a separate select because SQL*Plus
--versions 8.0.3 and 8.0.4 do not handle null LONG
--columns properly.
TTITLE SKIP 2 LEFT -
"DEFAULT VALUES FOR " s_owner_name '.' s_table_name -
SKIP 2
COLUMN default_value HEADING 'Default Value' FORMAT A40 WORD_WRAPPED
SELECT table_name,
column_name,
data_default default_value
FROM all_tab_columns
WHERE table_name = '&&s_table_name'
AND owner = '&&s_owner_name'
AND data_default IS NOT NULL
ORDER BY column_id;
--Clean up by undefining variables, clearing break and
--column definitions, and changing all settings back to
--their defaults.
UNDEFINE s_owner_name
UNDEFINE s_table_name
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF
SET ECHO OFF
SET VERIFY ON
SET FEEDBACK ON
SET RECSEP WRAPPED
SET HEADING ON
SET PAGESIZE 14
SET LONG 80
SET LINESIZE 80The first part of the script simply initializes a few settings and clears any previously existing column and break settings. The next part of the script, which is a bit more complex, breaks the first argument up so the owner’s name and the table name are in separate substitution variables. The script is intended to be invoked like this:
@LIST_COLUMNS [
ownertable_nameThe first argument, referenced by &&1, will contain a table name that is possibly qualified with an owner name. The first task of the script is to interpret this first argument. The following DECODE statement will return the table owner, if one was specified:
DECODE(INSTR('&&1','.'),
0,USER, /*Default to current user.*/
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,If no period is contained in the argument, then no owner name was explicitly specified, and DECODE will call the USER function to return the name of the currently logged on user. If a table owner was specified, the position of the period will be non-zero, causing DECODE to return the value of the last expression, which uses SUBSTR to return all characters before the period as the owner. A similar approach is taken to get the table name:
DECODE(INSTR('&&1','.'),
0,UPPER('&&1'), /*Only the table name was passed in.*/
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_nameThe issue here is that if a period was specified, the table name follows the period. Otherwise, the entire string is taken as the table name. The table name and owner name are selected from the DUAL table in order to get them into the substitution variables s_table_name and s_owner_name. The NEW_VALUE clause of the COLUMN command makes this happen.
The next part of the script is the SELECT statement, which returns information about each column in the table. The only complicated part of this query is the DECODE statement that starts out like this:
DECODE (data_type,
'VARCHAR2','VARCHAR2 (' || TO_CHAR(data_length) || ')',
'NVARCHAR2','NVARCHAR2 (' || TO_CHAR(data_length) || ')',
'CHAR','CHAR (' || TO_CHAR(data_length) || ')',
'NCHAR','NCHAR (' || TO_CHAR(data_length) || ')',
'NUMBER',
DECODE (data_precision,
NULL, 'NUMBER',
'NUMBER (' || TO_CHAR(data_precision)
|| ',' || TO_CHAR(data_scale) || ')'),
...The long DECODE expression exists because some datatypes have a length associated with them, some have a precision and scale, and some have neither. This DECODE statement contains one expression for each possible datatype, and that expression returns the appropriate information for that datatype. Consider the VARCHAR2 datatype, for example. All VARCHAR2 columns have a length associated with them. In order to display that length, the following two expressions are included in the DECODE statement:
'VARCHAR2','VARCHAR2 (' || TO_CHAR(data_length) || ')'The two expressions are separated by commas. The first expression evaluates to the string VARCHAR2. When DECODE is evaluating a datatype that matches that string, it will return the value of the second expression in the pair. In the case of VARCHAR2, that second expression is:
'VARCHAR2 (' || TO_CHAR(data_length) || ')'As you can see, this second expression concatenates the string `VARCHAR2 (` with the length, then follows that with a closing parentheses. The result will be a string resembling the one shown here:
VARCHAR2 (40)
The NUMBER and FLOAT datatypes add a bit more complexity. A NUMBER, for example, can be defined as floating-point or fixed-point. Floating-point numbers have null values for data_precision and data_scale. If a NUMBER field is floating-point, the data_precision will be null, and the nested DECODE will return just “NUMBER” as the datatype. Otherwise, the nested DECODE will return “NUMBER (precision, scale)”.
A second SELECT is done to return any default values defined for columns in the table. This is done in a separate query primarily because some versions of SQL*Plus delivered with early versions of Oracle8 (notably 8.0.3) generated errors whenever a LONG column contained a null value. The query in the preceding script avoids this problem by specifying “WHERE data_default IS NOT NULL” in the WHERE clause.
To run LIST_COLUMNS, execute the script from SQL*Plus and specify the table name as a command-line argument. You may optionally qualify the table name with an owner by using standard dot notation. The following example shows how to list columns for a table you own.
SQL> @list_columns employee
COLUMN LISTING FOR JEFF.EMPLOYEE
Column Name Data Type Nullable?
------------------------------ ----------------- ---------
EMPLOYEE_ID NUMBER NOT NULL
EMPLOYEE_NAME VARCHAR2 (40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_TERMINATION_DATE DATE
EMPLOYEE_BILLING_RATE NUMBER
DEFAULT VALUES FOR JEFF.EMPLOYEE
Column Name Default Value
------------------------------ ----------------------------------------
EMPLOYEE_HIRE_DATE sysdateThe following example shows how you can look at the column definitions for a table owned by another user, in this case the SYS user:
SQL> @list_columns sys.dual
COLUMN LISTING FOR SYS.DUAL
Column Name Data Type Nullable?
------------------------------ ----------------- ---------
DUMMY VARCHAR2 (1)