Using SQL to Generate SQL - MySQL

Most RDBMS products come with a set of catalog views that allow query access to the metadata that describes the database objects contained in the database. Experienced database administrators know how to use the data in the catalog to their advantage and actually use SQL statements to generate other SQL statements.

Generating SQL in Oracle

In Oracle, the USER_TABLES view contains information about each table that belongs to the current database user. You can use the command “DESCRIBE USER_TABLES” to see the definition of the view, or refer to the Oracle Server Reference manual for descriptions of this and other catalog views.

Here is an SQL statement that creates a DROP TABLE command for every table found in USER_TABLES. The WHERE clause was added to eliminate some Oracle internal tables that might otherwise appear in the result set. There are also techniques available for sending the query results to a file that can then be used as a script, but they are beyond the scope of this text (see the Oracle SQL*Plus SPOOL command for details).

SELECT 'DROP TABLE ' || TABLE_NAME ||
       ' CASCADE CONTRAINTS;' AS SQL
  FROM USER_TABLES
 WHERE TABLE_NAME NOT LIKE 'BIN$%';
SQL
-------------------------------------------
DROP TABLE MOVIE_RENTAL CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_TRANSACTION CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_ACCOUNT_PERSON CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE MOVIE_LANGUAGE CASCADE CONSTRAINTS;
DROP TABLE MOVIE_COPY CASCADE CONSTRAINTS;
DROP TABLE MOVIE CASCADE CONSTRAINTS;
DROP TABLE PERSON CASCADE CONSTRAINTS;
DROP TABLE MPAA_RATING CASCADE CONSTRAINTS;
DROP TABLE MOVIE_GENRE CASCADE CONSTRAINTS;
DROP TABLE LANGUAGE CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_ACCOUNT CASCADE CONSTRAINTS;

Generating SQL in Microsoft SQL Server

Microsoft uses the term “system tables” for the SQL Server tables that contain metadata. You can find descriptions of them in Books Online under the topic “system tables.” Here is the SQL Server equivalent of the previous example (dropping all tables) using the SQL Server SYSOBJECTS table. The WHERE clause filters out all objects except those with the “user table” object type.

SELECT 'DROP TABLE ' + NAME + ';'
  FROM SYSOBJECTS
 WHERE XTYPE='U'
DROP TABLE PERSON;
DROP TABLE MOVIE;
DROP TABLE MOVIE_COPY;
DROP TABLE MOVIE_LANGUAGE;
DROP TABLE EMPLOYEE;
DROP TABLE CUSTOMER_ACCOUNT_PERSON;
DROP TABLE CUSTOMER_TRANSACTION;
DROP TABLE MOVIE_RENTAL;
DROP TABLE CUSTOMER_ACCOUNT;
DROP TABLE LANGUAGE;
DROP TABLE MOVIE_GENRE;
DROP TABLE MPAA_RATING;

NOTE: These generated DROP statements won’t run without some additional work because of the referential constraints. SQL Server doesn’t support the “CASCADE CONSTRAINTS” clause with the DROP command, so the referential constraints would have to be dropped before the tables could be, or the DROP statements would have to be placed in the proper order before being run.