Search code examples
oracleoracle-sqldeveloper

Find table details in Oracle

I am having TEST schema and it has many tables.
I want to find table details like

Table name, column name, column datatype, column length, column default value, column allow null, column comment

I am using Oracle database, please guide me how to do this.
In SQL developer I am able to find these details for individual table but I want to get this for tables where table name starts with A,B or C (this can be any alphabet character)

Solution

  • If you are logged in as a DBA user, you can use:

    SELECT *
FROM   dba_tab_columns
WHERE  OWNER = 'TEST'
AND    SUBSTR(TABLE_NAME, 1, 1) IN ('A', 'B', 'C', 'a', 'b', 'c');

    Or you can query the all_tab_columns data dictionary view or, if you are logged in as the TEST user:

    SELECT *
FROM   user_tab_columns
WHERE  SUBSTR(TABLE_NAME, 1, 1) IN ('A', 'B', 'C', 'a', 'b', 'c');