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, commentsfrom all_tab_columns t1, all_col_comments t2where t1.table_name = t2.table_nameand t1.column_name = t2.column_nameand t1.table_name = 'TABLENAME'ORDER BY COLUMN_ID;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:
COLUMN_NAME DATA_TYPE NULL_STATUS COMMENTS------------------- -------------- ----------- -----------------------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.
