Script to get Table Structure in Oracle

 DECLARE

  x_table_header  VARCHAR2(50);

  x_data_type_row VARCHAR2(100);

  x_data_type     VARCHAR2(100);

  tab_count       NUMBER := 0;

  x_column_id     NUMBER := 0;

  CURSOR tab 

  IS

    SELECT ao.object_name

      FROM all_objects ao

     WHERE ao.object_type = 'TABLE'

       AND ao.object_name LIKE 'XX%';

  CURSOR c1(p_table_name VARCHAR2) 

  IS

    SELECT *

      FROM all_tab_columns atc

     WHERE atc.table_name = p_table_name

     ORDER BY column_id;

BEGIN

dbms_output.enable(10000000);  

FOR tbl IN tab 

LOOP

    

tab_count      := tab_count + 1;

x_table_header := 'CREATE TABLE ' || tbl.object_name || ' (';

   

dbms_output.put_line(x_table_header);

   

FOR tbl_cols IN c1(tbl.object_name) 

LOOP  

  

  SELECT MAX(atc.column_id)

INTO x_column_id

FROM all_tab_columns atc

   WHERE atc.table_name = tbl.object_name;

IF  tbl_cols.column_id <> x_column_id THEN

SELECT tbl_cols.data_type || DECODE(tbl_cols.data_type,

                                   'VARCHAR2',

                                   '(' || tbl_cols.data_length || '),',

                                   ',')

  INTO x_data_type

  FROM dual;

    

x_data_type_row := RPAD(tbl_cols.column_name,30,' ') || '      ' || x_data_type;

ELSIF  tbl_cols.column_id = x_column_id THEN

SELECT tbl_cols.data_type || DECODE(tbl_cols.data_type,

                                   'VARCHAR2',

                                   '(' || tbl_cols.data_length || '));',');')

  INTO x_data_type

  FROM dual;

x_data_type_row := RPAD(tbl_cols.column_name,30,' ') || '      ' || x_data_type;

END IF;

      

dbms_output.put_line(x_data_type_row);

     

  

END LOOP;

dbms_output.put_line(' ');

END LOOP;

END;