DDL Text

Create a PawSQL Workspace from text input by user. Workspaces are environments(tables, views and indexes) where you do the query optimization.

How to get DDL for tables

MySQL/Maria DB

You can get ddl for table, view, and the indexes on this table by executing following SQL statements against your database.

• Extract ddl for table tpch.customer

  SHOW CREATE table `tpch.customer`;

  and get the DDL of table tpch.customer. Copy to the left text area to create a workspace.

  CREATE TABLE `customer` (
  `C_CUSTKEY` int NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` int NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL,
   PRIMARY KEY `PK_IDX1614428511` (`C_CUSTKEY`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  
  -- tpch.orders definition
  CREATE TABLE `orders` (
  `O_ORDERKEY` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

PostgreSQL/Opengauss

• Extract tables in schema public in database tpch

  select
  array_to_string(array_agg(query), ';
  ')
  from
  (
  select
  table_name,
  'create table ' || table_name || ' (' || string_agg(concat( c1, c2, c3, c4, c5, c6 ), ', ')|| ')' as query
  from
  (
  select
  c.table_name,
  '"' || c.column_name || '"' || ' ' || case
  when data_type = 'array' then ltrim(udt_name, '_')|| '[]'
  else data_type
  end as c1,
  case
  when character_maximum_length > 0 then '(' || character_maximum_length || ')'
  end as c2,
  case
  when numeric_precision > 0
  and numeric_scale < 1 then null
  end as c3,
  case
  when numeric_precision > 0
  and numeric_scale > 0 then null
  end as c4,
  case
  when is_nullable = 'no' then ' not null'
  end as c5,
  case
  when column_default is not null
  and column_default not like 'nextval%' then ' default'
  end || ' ' || replace(column_default, '::character varying', '') as c6
  from
  information_schema.columns c,
  information_schema.tables t
  where
  c.table_catalog = 'tpch'
  and t.table_catalog = 'tpch'
  and t.table_schema = 'public'
  and c.table_name = t.table_name
  and c.table_schema = 'public'
  and t.table_type in ('base table')
  order by
  c.table_name,
  c.ordinal_position ) as string_columns
  group by
  table_name) dt  

• Extract indexes in schema public

     select
         array_to_string(array_agg(indexdef), ';
     ')
     from
  (
  select
      indexname,
      indexdef
  from
      pg_indexes
  where
      schemaname = 'public') tt

• Extract views in schema public

  select
      array_to_string(array_agg(def), '
  
  ')
  from
      (
      select
          viewname,
          'create view ' || viewname || ' as ' || definition as def
      from
          pg_catalog.pg_views pm
      where
          pm.schemaname = 'public') dt

• Extract materialized view in schema public

  select
   array_to_string(array_agg(def), '
  
  ')
  from
  (
  select
  matviewname,
  'create materialized view ' || matviewname || ' as
  ' || definition as def
  from
  pg_catalog.pg_matviews pm
  where
  pm.schemaname = 'public') dt

Oracle

• Extract tables in schema tpch in database tpch

     SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) || ';' 
     FROM all_tables
     WHERE owner = 'TPCH';

• Extract indexes in schema tpch

     SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) || ';'
     FROM all_indexes
     WHERE owner = 'TPCH';

• Extract views in schema tpch

     SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, owner) || ';'
     FROM all_views 
     WHERE owner = 'TPCH';

• Extract materialized view in schema tpch

     SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', mview_name,owner)  || ';'
     FROM all_mviews
     WHERE owner = 'TPCH';