Automatically Generating SQL Statements

In this last section, I ll briefly show you a technique of writing SQL statements that produce other SQL statements. This is very useful and can save you a lot of typing when writing SQL statements that are similar. One simple example is a SQL statement that produces DROP TABLE statements that remove tables from a database. The following query produces a series of DROP TABLE statements that drop the tables in the store schema:

  SELECT 'DROP TABLE '  table_name  ';'   FROM user_tables;  'DROPTABLE'TABLE_NAME';' ------------------------------------------ DROP TABLE COUPONS; DROP TABLE CUSTOMERS; DROP TABLE EMPLOYEES; DROP TABLE PRODUCTS; DROP TABLE PRODUCT_TYPES; DROP TABLE PROMOTIONS; DROP TABLE PURCHASES; DROP TABLE PURCHASES_TIMESTAMP_WITH_TZ; DROP TABLE PURCHASES_WITH_LOCAL_TZ; DROP TABLE PURCHASES_WITH_TIMESTAMP; DROP TABLE SALARY_GRADES; 

Note  

user_tables contains the details of the tables in the user s schema. The table_name column contains names of the tables.

You can spool the generated SQL statements to a file and use them later.