Find table details in Oracle-CodePudding

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)

CodePudding user response：

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');

CodePudding user response：

select * from ALL_TAB_COLUMNS
where 
OWNER = 'TEST'
and SUBSTR(TABLE_NAME, 1, 1) IN ('A', 'B', 'C', 'a', 'b', 'c');

this will give you column names and info of all tables accessible to the current user https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm