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, 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.

		
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: