4.4 How do I…Recreate CREATE TABLE statements?Problem
I need a SQL script to re-create one or more tables. I do not have any of the original statements used to create the tables. I know I can use the DESCRIBE command in SQL*Plus to list all the columns in the table, but I have many tables and the output of the DESCRIBE statement does not look like a CREATE TABLE statement. How can I generate scripts to re-create these tables?
Technique
The data dictionary view USER_TABLES contains all of the tables owned by a user account. The USER_TAB_COLUMNS data dictionary view contains each of the columns in the table. Unless the CREATE TABLE statement requires tablespace and storage information, only columns from the USER_TAB_COLUMNS view are needed. Figure 4.7 shows the columns in the USER_TAB_COLUMNS data dictionary view.
The TABLE_NAME column contains the name of the table to which the column belongs. The COLUMN_NAME column contains the name of the column as it was defined when the table was created. The DATA_TYPE column contains the datatype of the column.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP4_9.SQL, shown in Figure 4.8, contains a CREATE TABLE statement that will be rebuilt in this How-To.
Run the file to create the sample table.
SQL> START CHP4_9.sql
Table dropped.
Table created.
3. Turn off the heading and trailing statements and widen the length of the output line by setting the SQL*Plus environment variables.
SQL> SET HEADING OFF
SQL> SET LINESIZE 132
SQL> SET FEEDBACK OFF
The SET HEADING OFF command removes any column headings from the output generated by the query. The SET LINESIZE 132 command ensures that the output generated by the query does not wrap to multiple lines for a single row. The SET FEEDBACK OFF command suppresses the row counter that displays the number of rows returned by the query and the end of the output.
4. Load CHP4_10.SQL (seen in Figure 4.9) into the SQL buffer. This file contains a statement to query column information for the table we are trying to re-create.
Lines 1 through 4 contain the columns returned by the query. The TABLE_NAME column contains the name of the table. COLUMN_ID represents the order of the column in the table. COLUMN_NAME contains the name of the column. DATA_TYPE contains the data type of the column. Lines 5 and 6 contain the FROM clause of the query, specifying the USER_TAB_COLUMNS data dictionary view as the source of the query. The WHERE clause in line 8 returns records for the sample table created in Step 1. The ORDER BY clause in line 8 organizes the records in the order of the columns in the table.
5. Run the statement to display the results of the query.
This query looks somewhat like the SQL*Plus DESCRIBE statement. In the next step, the data will be formatted to look more like a SQL statement.
SQL> /
1 DEPT_NO NUMBER
2 DEPT_NAME VARCHAR2
3 CREATION_DATE DATE
6. Load CHP4_11.SQL (seen in Figure 4.10) into the SQL buffer. The file contains a statement to begin formatting the output to look like a SQL statement.
Lines 1 and 2 use the DECODE function to concatenate the CREATE TABLE keywords and the tablename to the beginning of the first output line. The COLUMN_ID field represents the order of the columns in the table. The DECODE function works like an in-line IF statement. If COLUMN_ID = 1, then display the keywords; otherwise, display a blank space.
7. Run the query to display the output from the previous step.
SQL> /
CREATE TABLE DEPT04 ( DEPT_NO NUMBER
DEPT_NAME VARCHAR2
CREATION_DATE DATE
The results are starting to look like a DDL statement. The CREATE TABLE keywords and the name of the table are only displayed on the first line of output.
8. Load CHP4_12.SQL (seen in Figure 4.11) into the SQL buffer. The query contained in this file handles the different data types possible in a table. The CHAR, VARCHAR2, and NUMBER data types require a length, scale, or precision modifier. All other data types do not require any more information. The DECODE function is used to put the correct modifiers with the data type. In Step 8, this statement will be combined with the statement created in the previous steps.
Lines 1 through 6 create a single column of output, creating the datatype of a column with the proper length, scale, and precision. Line 2 handles the VARCHAR2 datatype by concatenating the DATA_LENGTH column to the datatype. Lines 3 and 4 handle the NUMBER datatype by concatenating the DATA_PRECISION and DATA_SCALE columns to the datatype. Line 5 handles the CHAR datatype by concatenating the DATA_LENGTH column to the datatype.
9. Run the statement to view the output from the previous step.
SQL> /
NUMBER(8,0)
VARCHAR2(30)
DATE
Using the DECODE function to handle the possible data types generated is an important part of the statement. The query created in this step replaces the DATA_TYPE column in the previous query.
10. CHP4_13.SQL, shown in Figure 4.12, contains a combination of the two queries. When the queries are combined, the column name in the statement becomes long. To make the statement more readable, the columns are aliased and formatted in SQL*Plus.
Run the file using the START command.
SQL> START CHP4_13.sql
SQL> START CHP4_13.sql
CREATE TABLE DEPT04 ( DEPT_NO NUMBER(8,0)
DEPT_NAME VARCHAR2(30),
CREATION_DATE DATE,
Note that the ending parenthesis and the semicolon are missing. Although this looks easy to correct, it is the most complicated part of the process. A parenthesis and semicolon must be placed only after the last column of the table. The last column in the table is the column with the largest COLUMN_ID. The steps that follow will correct this problem.
11. Load CHP4_14.SQL (shown in Figure 4.13) into the SQL buffer. The file contains a query of USER_TAB_COLUMNS, which returns the highest numbered column in the table.
The query returns the value required, but it must be used as part of a DECODE statement within the query. The MAX function is a GROUP function. It works on all rows of the query and returns a single value. The query used to build the CREATE TABLE statements must return one row for each column in the table. This is not consistent with the use of a group function. To get around the problem, the table must be joined with itself, executing a GROUP statement on only one instance of the table. The next step shows a simplified version of this concept.
12. Load CHP4_15.SQL (seen in Figure 4.14) into the SQL buffer. The file contains a query that joins a table with itself to execute a GROUP BY function as part of a multirow query.
Line 1 selects the largest COLUMN_ID column from one instance of the table and the COLUMN_ID value from the other. Line 3 contains the same table twice in the FROM clause. The first instance of the table has the alias T1 and the second has T2. Oracle will treat them as two separate tables. Line 5 joins the two instances of the table together using the TABLE_NAME column. Line 6 forces the query to run for only the DEPT04 table. Line 7 contains a GROUP BY clause that is required to group data by the T1 instance of the table.
13. Execute the query to show a group by result on each line.
SQL> /
3 1
3 2
3 3
A number now exists that can be compared with the current COLUMN_ID to determine whether it is the last column in the table.
13. CHP4_16.SQL, shown in Figure 4.15, contains a SQL statement recreating a CREATE TABLE statement for a single table. Notice the additional DECODE function on the NUMBER type. This is to prevent parentheses from appearing when there is no precision defined for the column.
14. Execute the query using the START command.
SQL> START CHP4_16.sql
CREATE TABLE DEPT04 ( DEPT_NO NUMBER(8,0),
DEPT_NAME VARCHAR2(30),
CREATION_DATE DATE);
How It Works
Steps 1 and 2 create the DEPT04 sample table, used in this How-To. Step 3 sets the HEADING and FEEDBACK system variables to OFF to remove unwanted heading and trailing statements from the query results. Steps 4 and 5 query the column name and data type from the USER_TAB_COLUMNS view. The view contains all columns for tables owned by the current user account. Steps 6 and 7 use the DECODE function to format the query by concatenating the CREATE TABLE keywords to the beginning of the first column. Steps 8 and 9 use the DECODE function to create the scale and precision modifier for each of the columns. Step 10 uses the results from the prior steps to create a query with results very much like a CREATE TABLE statement. After these steps, a closing parenthesis and semicolon are all that is needed by the statement. Steps 11 through 13 present the method for joining a table to itself to determine the last column of the table. Step 14 puts this all together to create a final query.
Comments
It is not necessary to rewrite this script every time you need to re-create a table, or even type it in once. This book contains SQL scripts that you can use to re-create CREATE TABLE statements. SQL*Plus is a powerful tool for managing your database. Looking to the data dictionary and SQL*Plus to solve this type of problem saves you many hours. Re-creating CREATE TABLE statements would be a tedious job if done manually.