PLSQL Describe tables in schema - Oracle

At times, while we want to get a list of tables and their columns along with their data types from Oracle.

It is not so efficient to useDESC <tablename> for all the tables one after another.

I had a similar requirement and I came to know that DESC does not work within PLSQL.

I had a choice. to get the list tables and adding DESC before it and executing it later.

If you prefer to do that you can use the following SQL snippet to get all your tables in your schema

select 'desc '||table_name
from user_tables
order by 1

If you want to get all the tables across all the schema you can use all_tables instead of user_tables

select 'desc '||table_name
from all_tables
order by 1

once the result is printed with a table prefixed with desc you can execute all of them in bulk to get results.

The PLSQL Query without DESC to describe tables

Here is the PLSQL query that does the job without you having to copy, but the only problem here is the formatting but that can be matched by rewriting to suit your need.

set serveroutput on
begin
  FOR cursor1 IN (SELECT * FROM USER_TABLES ORDER BY TABLE_NAME)
  LOOP
    DBMS_OUTPUT.PUT_LINE('TABLE NAME :' || cursor1.TABLE_NAME);
    DBMS_OUTPUT.PUT_LINE('----------------------------');
    DBMS_OUTPUT.PUT_LINE('column_name,date_type,date_length');
    DBMS_OUTPUT.PUT_LINE('----------------------------');
      FOR cursor2 IN (select column_name, data_type, data_length from user_tab_columns where TABLE_NAME=cursor1.TABLE_NAME ORDER BY COLUMN_NAME)
        LOOP     
          DBMS_OUTPUT.PUT_LINE(cursor2.column_name || ',' || cursor2.data_type || ',' || cursor2.data_length  );
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('----------------------------');
        DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
end;

Here I have used some formatting elements like hyphens and commas you change it to suit your need

the major element to note here is that we are not using desc to describe the tables but using the user_tab_columns

you can use dba_tab_columns or all_tab_columns based on your need

to know more about these  Oracle views you can refer this article

Cheers
Sarav AK

---