Home / Questions / 119078
Accepted
nachfren
Asked: 2020-11-22 05:18:42 +0800 CST 2020-11-22 05:18:42 +0800 CST

Extract structure of all tables SQL Oracle

  • 772

With the following SELECTI see all the tables of a user:

SELECT table_name FROM all_tables where owner='usuario1'

Result:

TABLE_NAME
----------
USUARIOS
PRODUCTOS
CLIENTES
...(+300 tablas)

And with the sentence describeI see the structure of a table:

describe productos;

Result:

PRODUCTOS
Nombre               Nulo     Tipo           
-------------------- -------- -------------- 
id_productos         NOT NULL  NUMBER(5)
nombre_producto                VARCHAR2(255)
...

How could I unite these two functionalities?

I need to get the structure of all the tables which is almost 400 tables.

sql

1 Answers

  1. Best Answer

    For this you can use dmbs_metadata.get_ddl:

    select dbms_metadata.get_ddl('TABLE', table_name, owner)
from all_tables
where owner = 'usuario1'
    • 2