CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
David Kakauridze
David Kakauridze

Reputation: 315

Making "CREATE TABLE" statements from data of table

I have table TABLE_INFO with columns TNAME, CNAME, DTYPE, COLCOMMENT I need to create a SQL script in Oracle SQL Developer, which creates new table with table name from column TNAME, columns from CNAME, datatype of columns from DTYPE and column comment from COLCOMMENT How can I do this?

For example table TABLE_INFO has following data:

|TNAME | CNAME | DTYPE | Comment |

1|Employee | Emp_ID | NUMBER | Employee ID |

2|Employee | Emp_Nm| VARCHAR | Employee Name |

3|Job | Job_ID | NUMBER | Job ID | etc.

I need to write many CREATE TABLE statements like :

CREATE TABLE EMLOYEE (EMP ID NUMBER, Emp_Nm VARCHAR)

COMMENT ON COLUMN EMP_ID is ''Employee ID''

COMMENT ON COLUMN EMP_NM is ''Employee NAME''

for each TNAME

Upvotes: 0

Views: 267

Answers (3)

David Kakauridze
David Kakauridze

Reputation: 315

I've found anouther solution

CREATE OR REPLACE

PROCEDURE CREATEFROMTABLE AS

BEGIN

    for j in (select DISTINCT tname from t_info) loop `

    dbms_output.put_line('create table '|| j.tname || ' as (');`

                for col in (select CNAME, dtype from T_INFO where tname = j.tname) 
                loop
                        dbms_output.put_line(col.cname|| '   ' || col.dtype);
                end loop;

               dbms_output.put_line(')');`

               dbms_output.put_line('');`

                for col in (select cname, dcmt from T_INFO where tname = j.tname) `

                loop`

                    dbms_output.put_line('COMMENT ON COLUMN '|| col.cname || ' is ' || col.dcmt || ' ');`

               end loop;    `

    `dbms_output.put_line(' ');`

    `end loop;`

END CREATEFROMTABLE;

t_info is main table with columns tname, cname, dcmt

tname is new table name

cname is column name

dcmt is comment on the column

Upvotes: 0

kangbu
kangbu

Reputation: 196

Oh, I confused.

This is a code for schema:

CREATE TABLE TABLE_INFO
(
    TNAME        VARCHAR2 (30)
   ,CNAME        VARCHAR2 (30)
   ,DTYPE        VARCHAR2 (30)
   ,COLCOMMENT   VARCHAR2 (100)
);

INSERT INTO TABLE_INFO VALUES ('Employee','Emp_ID','NUMBER','Employee ID');
INSERT INTO TABLE_INFO VALUES ('Employee','Emp_Nm','VARCHAR','Employee Name');
INSERT INTO TABLE_INFO VALUES ('Employee','Emp_Sal','NUMBER','Employee Salary');
INSERT INTO TABLE_INFO VALUES ('Job','Job_ID','NUMBER','Job ID');
INSERT INTO TABLE_INFO VALUES ('Job','Job_Nm','VARCHAR','Job Name');
INSERT INTO TABLE_INFO VALUES ('Department','Dept_ID','VARCHAR','Department ID');
INSERT INTO TABLE_INFO VALUES ('Department','Dept_Nm','VARCHAR','Department Name');

And, this is a code for execution:

-- FOR TABLE CREATION
SELECT 
 CASE WHEN RNO = 1 THEN 'CREATE TABLE '||T1.TNAME||' ( ' ELSE '    ' END ||
 T2.CNAME || ' ' || T2.DTYPE ||
 CASE WHEN CNT = RNO THEN ');' ELSE ',' END AS SQLTEXT
  FROM (  SELECT TNAME
                ,COUNT (1) CNT
            FROM TABLE_INFO
        GROUP BY TNAME) T1
      ,(SELECT ROW_NUMBER () OVER (PARTITION BY TNAME ORDER BY CNAME) RNO
              ,X.*
          FROM TABLE_INFO X) T2
 WHERE T1.TNAME = T2.TNAME
ORDER BY T1.TNAME, T2.RNO;

-- FOR COMMENT CREATION
SELECT 'COMMENT ON TABLE ' || TNAME || '.' || CNAME || ' IS ''' || COLCOMMENT || ''';' AS SQLTEXT
  FROM TABLE_INFO;

You could find the result at here

NOTE: I used ROW_NUMBER () function at Oracle only. please be aware of that.

Upvotes: 1

kangbu
kangbu

Reputation: 196

Try this.

SELECT T1.TABLE_NAME
      ,T1.COLUMN_NAME
      ,T1.DATA_TYPE
      ,T2.COMMENTS
  FROM ALL_TAB_COLS T1
      ,ALL_COL_COMMENTS T2
 WHERE T1.OWNER = T2.OWNER
   AND T1.TABLE_NAME = T2.TABLE_NAME
   AND T1.COLUMN_NAME = T2.COLUMN_NAME

It will retrieve all accessible tables information.

To include DBA tables, use DBA_TAB_COLS and DBA_COL_COMMENTS instead of ALL_TAB_COLS and ALL_COL_COMMENTS respectively.

You can also give more conditions to that query.

To insert this,

INSERT INTO TABLE_INFO (
                TNAME
               ,CNAME
               ,DTYPE
               ,COLCOMMENT)
    SELECT T1.TABLE_NAME
          ,T1.COLUMN_NAME
          ,T1.DATA_TYPE
          ,T2.COMMENTS
      FROM ALL_TAB_COLS T1
          ,ALL_COL_COMMENTS T2
     WHERE T1.OWNER = T2.OWNER
       AND T1.TABLE_NAME = T2.TABLE_NAME
       AND T1.COLUMN_NAME = T2.COLUMN_NAME

Upvotes: 0

Related Questions