Toad World® Forums

Table and constraint definition to spreadsheet, csv

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT

from USER_TAB_COLUMNS

where TABLE_NAME = ‘table_name’

(ref: http://www.eveandersson.com/writing/data-model-reverse-engineering)

 

SELECT DBMS_METADATA.get_ddl (UPPER (‘TABLE’),

UPPER (‘table_name’),

UPPER (‘owner’)

)

FROM DUAL;

Hi everyone,

I want to export all table definition (column, data type, etc) as well as constraints to a spreadsheet.

So basically I want to see a definition of all my tables in Excel with the columns and their definitions along with the constraints.

I am NOT looking for any of the following (which is what my search results in the help files and online have yielded):

Copy table definition into another database
Export table data from Oracle
Import from Excel

So, I think I have figured out how to obtain this information using a few statements per table. I was just wondering if there is a way to do this for the entire database and save myself some time on 100+ tables.

These are the statements I’m using:

(ref: from Toad World)

The first select gives me the columns and their definitions in a grid - easy to copy and paste into Excel.

The second select gives me the constraint information and the tables but in a create statement.

Is there anything in TOAD or data modeler or anywhere that will help me with this? Can I convert the Create statement into a columns/row format easily?

Otherwise, I am getting the info I need but will have to format it for each table.

Thanks everyone! (^_^)

Kel