How to extract table definitions using SQL or Toad

Question

Can somebody tell me how to extract my table definitions using SQL? I want to extract the datatypes of all my tables and other information from my Oracle schema. I have about 100 tables.

I need the complete documentation of my Oracle Schema. My schema name IS "cco".

Can I do this by SQL?

I am using Toad for Data analyst 3.3. Please let me know if this tool helps.

Answer 1

You can try this -

select * from all_tab_cols
where owner = 'CCO';

Answer 2

To get the DDL for all tables of the current user, you can use this:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables;

You will need to adjust your SQL client to be able to properly display the content of a CLOB column.

More details (e.g. about how to get the DDL for other objects) can be found in the manual: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm

Answer 3

you can use the table:USER_TAB_COLUMNS

Find below query example

select 
table_name,
column_name,
data_type,
data_length,
data_precision,
nullable
from USER_TAB_COLUMNS 
where table_name = '<table_name>';

This is only an example you can also do a select * to get more information.

you can also use the table: all_tab_columns

For a better display you can use:

    select table_name,column_name, data_type||
case
when data_precision is not null and nvl(data_scale,0)>0 then '('||data_precision||','||data_scale||')'
when data_precision is not null and nvl(data_scale,0)=0 then '('||data_precision||')'
when data_precision is null and data_scale is not null then '(*,'||data_scale||')'
when char_length>0 then '('||char_length|| case char_used 
                                                         when 'B' then ' Byte'
                                                         when 'C' then ' Char'
                                                         else null 
                                           end||')'
end||decode(nullable, 'N', ' NOT NULL') as data_type
from user_tab_columns
where table_name = '<TABLE_NAME>';