Breaking News: Grepper is joining You.com. Read the official announcement!

get all table ddl sql oracle 19c

jABERIT 786 answered on May 4, 2023 Popularity 1/10 Helpfulness 1/10

answer get all table ddl sql oracle 19c

get all table ddl sql oracle 19c

Comment

/* Formatted on 5/4/2023 4:02:15 PM (QP5 v5.391) */
SET ECHO OFF
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET LINESIZE 132

DEFINE schema=&1
DEFINE CR=chr(10)
DEFINE TAB=chr(9)
COL x NOPRINT
COL y NOPRINT

SELECT table_name y, 0 x, 'CREATE TABLE ' || RTRIM (table_name) || '('
  FROM dba_tables
 WHERE owner = UPPER ('&schema')
UNION
SELECT tc.table_name
           y,
       column_id
           x,
          DECODE (column_id, 1, '    ', '   ,')
       || RTRIM (column_name)
       || &TAB
       || &TAB
       || RTRIM (data_type)
       || RTRIM (
              DECODE (
                  data_type,
                  'DATE', NULL,
                  'LONG', NULL,
                  'NUMBER', DECODE (TO_CHAR (data_precision),
                                    NULL, NULL,
                                    '('),
                  '('))
       || RTRIM (
              DECODE (
                  data_type,
                  'DATE', NULL,
                  'CHAR', data_length,
                  'VARCHAR2', data_length,
                  'NUMBER', DECODE (
                                TO_CHAR (data_precision),
                                NULL, NULL,
                                   TO_CHAR (data_precision)
                                || ','
                                || TO_CHAR (data_scale)),
                  'LONG', NULL,
                  '******ERROR'))
       || RTRIM (
              DECODE (
                  data_type,
                  'DATE', NULL,
                  'LONG', NULL,
                  'NUMBER', DECODE (TO_CHAR (data_precision),
                                    NULL, NULL,
                                    ')'),
                  ')'))
       || &TAB
       || &TAB
       || RTRIM (DECODE (nullable, 'N', 'NOT NULL', NULL))
  FROM dba_tab_columns tc, dba_objects o
 WHERE     o.owner = tc.owner
       AND o.object_name = tc.table_name
       AND o.object_type = 'TABLE'
       AND o.owner = UPPER ('&schema')
UNION
SELECT table_name
           y,
       999999
           x,
          ')'
       || &CR
       || '  STORAGE('
       || &CR
       || '  INITIAL '
       || initial_extent
       || &CR
       || '  NEXT '
       || next_extent
       || &CR
       || '  MINEXTENTS '
       || min_extents
       || &CR
       || '  MAXEXTENTS '
       || max_extents
       || &CR
       || '  PCTINCREASE '
       || pct_increase
       || ')'
       || &CR
       || '  INITRANS '
       || ini_trans
       || &CR
       || '  MAXTRANS '
       || max_trans
       || &CR
       || '  PCTFREE '
       || pct_free
       || &CR
       || '  PCTUSED '
       || pct_used
       || &CR
       || '  PARALLEL (DEGREE '
       || RTRIM (DEGREE)
       || ') '
       || &CR
       || '  TABLESPACE '
       || RTRIM (tablespace_name)
       || &CR
       || '/'
       || &CR
       || &CR
  FROM dba_tables
 WHERE owner = UPPER ('CDMS_BASE')
ORDER BY 1, 2;

Popularity 1/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: ddl get oracle sql

Link to this answer
Share Copy Link

Contributed on May 04 2023

jABERIT 786

0 Answers Avg Quality 2/10

get all table ddl sql oracle 19c

Contents

More Related Answers

get all table ddl sql oracle 19c

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.