Exporting a report on tables' structure in Oracle

[Muammar]

Hey everyone, I'm using Oracle 10.2g with PL/SQL Developer 7.1 from Allround Automation and I want to list my tables one by one showing each detailing its fields.. Any simple way to do this?? Many thanks!

---

We will either find a way, or make one!   (\ /)   (- .-) C(''')(''')

[David Mujica]

The only way I've done it in the past was write some PL/SQL code that would loop through tabels like: SELECT * FROM ALL_TABLES WHERE OWNER='myOwner ORDER BY TABLE_NAME;

[Muammar]

Exactly what I've done

CREATE OR REPLACE VIEW schema_tables AS
SELECT o.object_type AS object_type
, c.table_name AS table_name
, c.column_id AS column_id
, c.column_name AS column_name
, DECODE(c.nullable,'N','NOT NULL','') AS nullable
, DECODE(c.data_type
, 'BFILE' ,'BINARY FILE LOB'
, 'BINARY_FLOAT' ,c.data_type
, 'BINARY_DOUBLE',c.data_type
, 'BLOB' ,c.data_type
, 'CLOB' ,c.data_type
, 'CHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'DATE' ,c.data_type
, 'FLOAT' ,c.data_type
, 'LONG RAW' ,c.data_type
, 'NCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'NVARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'NUMBER' ,DECODE(NVL(c.data_precision||c.data_scale,0)
, 0,c.data_type
, DECODE(NVL(c.data_scale,0),0
, c.data_type||'('||c.data_precision||')'
, c.data_type||'('||c.data_precision||','|| c.data_scale||')'))
, 'RAW' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'VARCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'VARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'TIMESTAMP' , c.data_type,c.data_type) AS data_type
, CASE WHEN c.data_default IS NULL THEN 'N' ELSE 'Y' END AS data_default
FROM user_tab_columns c,user_objects o
WHERE o.object_name = c.table_name
ORDER BY c.table_name, c.column_id
/
set feed off markup html on spool on
/
spool 'c:\filename.htm'
/
select * from schema_tables
/
spool off
/
set markup html off spool off
/

---

We will either find a way, or make one!   (\ /)   (- .-) C(''')(''')

[Jorgen Andersson]

Use:

dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Example:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

Gives you all the tables and indexes for the logged in user.