Get a Table Description in Oracle’s PL/SQL

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:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes