Script for automatic creation of DB tables

Question

I have to make a script which creates schema that must have 1.000 tables, with 1.000 columns. Table name (example): TABLE_058 Column name (example): T058_COL_078

Tables should be empty. I'm working with Oracle DB and ain't too apt with SQL/PL-SQL. Would be much obliged if someone would point me in right direction.

Answer 1

This will work if you save it as a script and then execute it under SQL*Plus. The tables are named TABLE_000 through TABLE_999 and the columns are similarly sequenced 000 through 999.

SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET LINESIZE 2000
SET FEEDBACK OFF
SPOOL C:\CreateTables.sql
SELECT
 CASE
 WHEN ColIndex = 0 THEN 'CREATE TABLE TABLE_' || TO_CHAR(TableIndex, 'FM000') || ' ('
 ELSE NULL
 END ||
 ' T' || TO_CHAR(TableIndex, 'FM000') || '_COL_' || TO_CHAR(ColIndex, 'FM000') || ' VARCHAR2(1)' ||
 CASE
 WHEN ColIndex = 999 THEN ');'
 ELSE ','
 END
FROM (
 SELECT TableIndex, ColIndex FROM (
 SELECT LEVEL - 1 AS TableIndex FROM DUAL CONNECT BY LEVEL <= 1000)
 CROSS JOIN (
 SELECT LEVEL - 1 AS ColIndex FROM DUAL CONNECT BY LEVEL <= 1000)
 ORDER BY TableIndex, ColIndex);
SPOOL OFF

Some things to note:

• The numbering scheme is from 000 through 999 because your template for table/column names uses three digits and the only way to get to 1000 tables/columns like that is to start at zero.

• Change the filename in SPOOL C:\CreateTables.sql to a filename that works for you.

• You didn't specify the column type so the script above has them all as VARCHAR2(1)

• It's important to run the above as a script from SQL*Plus. If you don't, a lot of the SQL*Plus chatter will end up in the spooled output. To run the script from SQL*Plus, just type the "at" sign (@) followed by the script's name. If you name it TableGenScript.sql then do this:

  SQL> @TableGenScript.sql
  

The first few lines of output from the script look like this:

CREATE TABLE TABLE_000 ( T000_COL_000 VARCHAR2(1),
 T000_COL_001 VARCHAR2(1),
 T000_COL_002 VARCHAR2(1),
 T000_COL_003 VARCHAR2(1),

Give it a try and you should be able to tweak this to your specific needs.

---

Addendum NikolaB asked how to vary the column type and the answer is too long to fit in a comment...

To vary the column type, take the part of the query that says || ' VARCHAR2(1)' || and replace it with your data-type logic. For example, if columns 0-599 are VARCHAR2, columns 600-899 are NUMBER, and columns 900-999 are DATE, change the script to something like this:

... all the SETs like above, then the SPOOL ...
SELECT
 CASE
 WHEN ColIndex = 0 THEN 'CREATE TABLE TABLE_' || TO_CHAR(TableIndex, 'FM000') || ' ('
 ELSE NULL
 END ||
 ' T' || TO_CHAR(TableIndex, 'FM000') || '_COL_' || TO_CHAR(ColIndex, 'FM000') ||
 CASE -- put the data-type logic in this CASE
 WHEN ColIndex BETWEEN 0 AND 599 THEN ' VARCHAR2(1)'
 WHEN ColIndex BETWEEN 600 AND 899 THEN ' NUMBER'
 ELSE ' DATE'
 END || -- data-type logic ends here and original query resumes
 CASE
 WHEN ColIndex = 999 THEN ');'
 ELSE ','
 END
FROM
... and then the same as above, all the way through to the SPOOL OFF

I've highlighted the CASE statement with a comment. If you put your data-type logic between the CASE and the END you should be fine.

• NikolaB: Thank you very much! I'm having difficulty customizing script, do you have some useful link from where i can learn about syntax. I have to have three types of column data(varchar2,date,number) but i can't write it without errors. This makes me feel stupid it sounds so simple.
• Ed Gibbs: @NikolaB - this is far from simple so don't feel dumb. I don't think you'll find any targeted help on the web so I've updated my answer. Check the "Addendum" toward the end for help with varying the data type.

Answer 2

Export the schema's metadata.

exp userid=user/pass@db owner=someowner rows=n file=somefile.dmp

if you open the file using notepad you could see the DML statements. you can then import using

imp userid=user/pass@otherdb file=somefile.dmp full=y

you can also copy to another schema on the same db (usually for testing)

imp userid=user/pass@db file=somefile.dmp fromuser=someschema touser=otherschema

you can also use the new datapump for parallel and compression enhancements. check out this link