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