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.

Inbreed

If you don't have lots of table you could use toad for data analyst to view the datatype of all the tables. You can do it from object explorer, double click on table name and you'll find the required info on the new windown in tab column. – Bassarisk

I have 100+ tables. Now i want to extract all the table information for the documentation purpose – Inbreed

@santosha i have posted the query to get the datatypes of tables. – Bassarisk

Answer 1

You can try this -

select * from all_tab_cols
where owner = 'CCO';

Redman

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

Fraunhofer

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

Bassarisk

i have added a piece of sql where you can paste the result on your document. – Bassarisk