RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Intermediate
Jul 14, 2009

Get a Table Description in Oracle's PL/SQL

If you're with an organization that requires you to document everything that you do (and if you're not required to, there's something wrong!), you'll no doubt have to document any database changes and additions you make. But when some table hasn't been documented, you can easily keep your audit trail complete.

Oracle maintains table descriptions in the meta table all_tab_columns. The information includes field names and types, whether they are nullable or not, etc. You'll also find any user comments (if any) about table columns in the meta table all_col_comments. For any specified table, the following PL/SQL produces a small report detailing each column, the type, and any comments:

select t1.column_name, 
   substr(data_type||'('||data_length||')', 0, 20) as data_type, 
   decode(nullable,'N','NOT NULL', '') as null_status, comments
from all_tab_columns t1, all_col_comments t2
where t1.table_name = t2.table_name
and t1.column_name = t2.column_name
and t1.table_name = 'TABLENAME'

The preceding code selects all the column names, datatypes, maximum sizes (truncating that to 20 characters—for reasons known only to Oracle developers, the data_type column is 107 characters long!), whether the column is nullable or may not contain null, and any comments. It links the two meta tables by the specified table and column names, and sorts the output by the column ID in all_tab_columns. Change TABLENAME to the name of the table for which you wish to produce a summary.

The output should look similar to this:

------------------- -------------- ----------- -----------------------
EMPLOYEE#           VARCHAR2(8)    NOT NULL    Employee number.
USERNAME            VARCHAR2(30)   NOT NULL    The username for access 
                                               to computers and databases.
EMAIL_ADDRESS       VARCHAR2(30)   NOT NULL    The email address for 
                                               CSU employees.
INITIAL_PASSWORD    VARCHAR2(20)   NOT NULL    When a username is added 
                                               to a machine or database, 
                                               this is the initial password 
                                               given to the employee.
DATE_DELETED        DATE(7)                    Date username is removed 
                                               from the system.
Aaron Mason
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date