Home arrow MySQL arrow Page 2 - Advanced Query Writing, concluded

Using SQL to Generate SQL - MySQL

This article, the second of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. Advanced Query Writing, concluded
  2. Using SQL to Generate SQL
  3. The CASE Expression
  4. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 8
March 09, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: