Table of Contents for

Learning SQL, 2nd Edition

While some project teams include a full-time database designer who oversees the design and implementation of the database, many projects take the “design-by-committee” approach, allowing multiple people to create database objects. After several weeks or months of development, you may need to generate a script that will create the various tables, indexes, views, and so on that the team has deployed. Although a variety of tools and utilities will generate these types of scripts for you, you can also query the information_schema views and generate the script yourself.

As an example, let’s build a script that will create the bank.customer table. Here’s the command used to build the table, which I extracted from the script used to build the example database:

create table customer
 (cust_id integer unsigned not null auto_increment,
  fed_id varchar(12) not null,
  cust_type_cd enum('I','B') not null,
  address varchar(30),
  city varchar(20),
  state varchar(20),
  postal_code varchar(10),
  constraint pk_customer primary key (cust_id)
 );

Although it would certainly be easier to generate the script with the use of a procedural language (e.g., Transact-SQL or Java), since this is a book about SQL I’m going to write a single query that will generate the create table statement. The first step is to query the information_schema.columns table to retrieve information about the columns in the table:

mysql> SELECT 'CREATE TABLE customer (' create_table_statement
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->  (SELECT concat('  ',column_name, ' ', column_type,
    ->     CASE
    ->       WHEN is_nullable = 'NO' THEN ' not null'
    ->       ELSE ''
    ->     END,
    ->     CASE
    ->       WHEN extra IS NOT NULL THEN concat(' ', extra)
    ->       ELSE ''
    ->     END,
    ->     ',') txt
    ->   FROM information_schema.columns
    ->   WHERE table_schema = 'bank' AND table_name = 'customer'
    ->   ORDER BY ordinal_position
    ->  ) cols
    -> UNION ALL
    -> SELECT ')';
+-----------------------------------------------------+
| create_table_statement                              |
+-----------------------------------------------------+
| CREATE TABLE customer (                             |
|   cust_id int(10) unsigned not null auto_increment, |
|   fed_id varchar(12) not null ,                     |
|   cust_type_cd enum('I','B') not null ,             |
|   address varchar(30) ,                             |
|   city varchar(20) ,                                |
|   state varchar(20) ,                               |
|   postal_code varchar(10) ,                         |
| )                                                   |
+-----------------------------------------------------+
9 rows in set (0.04 sec)

Well, that got us pretty close; we just need to add queries against the table_constraints and key_column_usage views to retrieve information about the primary key constraint:

mysql> SELECT 'CREATE TABLE customer (' create_table_statement
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->  (SELECT concat('  ',column_name, ' ', column_type,
    ->     CASE
    ->       WHEN is_nullable = 'NO' THEN ' not null'
    ->       ELSE ''
    ->     END,
    ->     CASE
    ->       WHEN extra IS NOT NULL THEN concat(' ', extra)
    ->       ELSE ''
    ->     END,
    ->     ',') txt
    ->   FROM information_schema.columns
    ->   WHERE table_schema = 'bank' AND table_name = 'customer'
    ->   ORDER BY ordinal_position
    ->  ) cols
    -> UNION ALL
    -> SELECT concat('  constraint primary key (')
    -> FROM information_schema.table_constraints
    -> WHERE table_schema = 'bank' AND table_name = 'customer'
    ->   AND constraint_type = 'PRIMARY KEY'
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->  (SELECT concat(CASE WHEN ordinal_position > 1 THEN '   ,'
    ->     ELSE '    ' END, column_name) txt
    ->   FROM information_schema.key_column_usage
    ->   WHERE table_schema = 'bank' AND table_name = 'customer'
    ->     AND constraint_name = 'PRIMARY'
    ->   ORDER BY ordinal_position
    ->  ) cols
    -> UNION ALL
    -> SELECT '  )'
    -> UNION ALL
    -> SELECT ')';
+-----------------------------------------------------+
| create_table_statement                              |
+-----------------------------------------------------+
| CREATE TABLE customer (                             |
|   cust_id int(10) unsigned not null auto_increment, |
|   fed_id varchar(12) not null ,                     |
|   cust_type_cd enum('I','B') not null ,             |
|   address varchar(30) ,                             |
|   city varchar(20) ,                                |
|   state varchar(20) ,                               |
|   postal_code varchar(10) ,                         |
|   constraint primary key (                          |
|     cust_id                                         |
|   )                                                 |
| )                                                   |
+-----------------------------------------------------+
12 rows in set (0.02 sec)

To see whether the statement is properly formed, I’ll paste the query output into the mysql tool (I’ve changed the table name to customer2 so that it won’t step on our other table):

mysql> CREATE TABLE customer2 (
    ->   cust_id int(10) unsigned not null auto_increment,
    ->   fed_id varchar(12) not null ,
    ->   cust_type_cd enum('I','B') not null ,
    ->   address varchar(30) ,
    ->   city varchar(20) ,
    ->   state varchar(20) ,
    ->   postal_code varchar(10) ,
    ->   constraint primary key (
    ->     cust_id
    ->   )
    -> );
Query OK, 0 rows affected (0.14 sec)

The statement executed without errors, and there is now a customer2 table in the bank database. In order for the query to generate a well-formed create table statement for any table, more work is required (such as handling indexes and foreign key constraints), but I’ll leave that as an exercise.

Many organizations allow for database maintenance windows, wherein existing database objects may be administered (such as adding/dropping partitions) and new schema objects and code can be deployed. After the deployment scripts have been run, it’s a good idea to run a verification script to ensure that the new schema objects are in place with the appropriate columns, indexes, primary keys, and so forth. Here’s a query that returns the number of columns, number of indexes, and number of primary key constraints (0 or 1) for each table in the bank schema:

mysql> SELECT tbl.table_name,
    ->  (SELECT count(*) FROM information_schema.columns clm
    ->   WHERE clm.table_schema = tbl.table_schema
    ->     AND clm.table_name = tbl.table_name) num_columns,
    ->  (SELECT count(*) FROM information_schema.statistics sta
    ->   WHERE sta.table_schema = tbl.table_schema
    ->     AND sta.table_name = tbl.table_name) num_indexes,
    ->  (SELECT count(*) FROM information_schema.table_constraints tc
    ->   WHERE tc.table_schema = tbl.table_schema
    ->     AND tc.table_name = tbl.table_name
    ->     AND tc.constraint_type = 'PRIMARY KEY') num_primary_keys
    -> FROM information_schema.tables tbl
    -> WHERE tbl.table_schema = 'bank' AND tbl.table_type = 'BASE TABLE'
    -> ORDER BY 1;
+--------------+-------------+-------------+------------------+
| table_name   | num_columns | num_indexes | num_primary_keys |
+--------------+-------------+-------------+------------------+
| account      |          11 |           6 |                1 |
| branch       |           6 |           1 |                1 |
| business     |           4 |           1 |                1 |
| customer     |           7 |           1 |                1 |
| department   |           2 |           2 |                1 |
| employee     |           9 |           4 |                1 |
| individual   |           4 |           1 |                1 |
| officer      |           7 |           2 |                1 |
| product      |           5 |           2 |                1 |
| product_type |           2 |           1 |                1 |
| transaction  |           8 |           4 |                1 |
+--------------+-------------+-------------+------------------+
11 rows in set (13.83 sec)

You could execute this statement before and after the deployment and then verify any differences between the two sets of results before declaring the deployment a success.

Some languages, such as Oracle’s PL/SQL and Microsoft’s Transact-SQL, are supersets of the SQL language, meaning that they include SQL statements in their grammar along with the usual procedural constructs, such as “if-then-else” and “while.” Other languages, such as Java, include the ability to interface with a relational database, but do not include SQL statements in the grammar, meaning that all SQL statements must be contained within strings.

Therefore, most relational database servers, including SQL Server, Oracle Database, and MySQL, allow SQL statements to be submitted to the server as strings. Submitting strings to a database engine rather than utilizing its SQL interface is generally known as dynamic SQL execution. Oracle’s PL/SQL language, for example, includes an execute immediate command, which you can use to submit a string for execution, while SQL Server includes a system stored procedure called sp_executesql for executing SQL statements dynamically.

MySQL provides the statements prepare, execute, and deallocate to allow for dynamic SQL execution. Here’s a simple example:

mysql> SET @qry = 'SELECT cust_id, cust_type_cd, fed_id FROM customer';
Query OK, 0 rows affected (0.07 sec)

mysql> PREPARE dynsql1 FROM @qry;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE dynsql1;
+---------+--------------+-------------+
| cust_id | cust_type_cd | fed_id      |
+---------+--------------+-------------+
|       1 | I            | 111-11-1111 |
|       2 | I            | 222-22-2222 |
|       3 | I            | 333-33-3333 |
|       4 | I            | 444-44-4444 |
|       5 | I            | 555-55-5555 |
|       6 | I            | 666-66-6666 |
|       7 | I            | 777-77-7777 |
|       8 | I            | 888-88-8888 |
|       9 | I            | 999-99-9999 |
|      10 | B            | 04-1111111  |
|      11 | B            | 04-2222222  |
|      12 | B            | 04-3333333  |
|      13 | B            | 04-4444444  |
|      99 | I            | 04-9999999  |
+---------+--------------+-------------+
14 rows in set (0.27 sec)

mysql> DEALLOCATE PREPARE dynsql1;
Query OK, 0 rows affected (0.00 sec)

The set statement simply assigns a string to the qry variable, which is then submitted to the database engine (for parsing, security checking, and optimization) using the prepare statement. After executing the statement by calling execute, the statement must be closed using deallocate prepare, which frees any database resources (e.g., cursors) that have been utilized during execution.

The next example shows how you could execute a query that includes placeholders so that conditions can be specified at runtime:

mysql> SET @qry = 'SELECT product_cd, name, product_type_cd, date_offered, date_
retired FROM product WHERE product_cd = ?';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE dynsql2 FROM @qry;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @prodcd = 'CHK';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql2 USING @prodcd;
+------------+------------------+-----------------+--------------+-------------+
| product_cd | name             | product_type_cd | date_offered | date_retired|
+------------+------------------+-----------------+--------------+-------------+
| CHK        | checking account | ACCOUNT         | 2004-01-01   | NULL        |
+------------+------------------+-----------------+--------------+-------------+
1 row in set (0.01 sec)

mysql> SET @prodcd = 'SAV';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql2 USING @prodcd;
+------------+-----------------+-----------------+--------------+--------------+
| product_cd | name            | product_type_cd | date_offered | date_retired |
+------------+-----------------+-----------------+--------------+--------------+
| SAV        | savings account | ACCOUNT         | 2004-01-01   | NULL         |
+------------+-----------------+-----------------+--------------+--------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE dynsql2;
Query OK, 0 rows affected (0.00 sec)

In this sequence, the query contains a placeholder (the ? at the end of the statement) so that the product code can be submitted at runtime. The statement is prepared once and then executed twice, once for product code 'CHK' and again for product code 'SAV', after which the statement is closed.

What, you may wonder, does this have to do with metadata? Well, if you are going to use dynamic SQL to query a table, why not build the query string using metadata rather than hardcoding the table definition? The following example generates the same dynamic SQL string as the previous example, but it retrieves the column names from the information_schema.columns view:

mysql> SELECT concat('SELECT ',
    ->   concat_ws(',', cols.col1, cols.col2, cols.col3, cols.col4,
    ->     cols.col5, cols.col6, cols.col7, cols.col8, cols.col9),
    ->   ' FROM product WHERE product_cd = ?')
    -> INTO @qry
    -> FROM
    ->  (SELECT
    ->     max(CASE WHEN ordinal_position = 1 THEN column_name
    ->       ELSE NULL END) col1,
    ->     max(CASE WHEN ordinal_position = 2 THEN column_name
    ->       ELSE NULL END) col2,
    ->     max(CASE WHEN ordinal_position = 3 THEN column_name
    ->       ELSE NULL END) col3,
    ->     max(CASE WHEN ordinal_position = 4 THEN column_name
    ->       ELSE NULL END) col4,
    ->     max(CASE WHEN ordinal_position = 5 THEN column_name
    ->       ELSE NULL END) col5,
    ->     max(CASE WHEN ordinal_position = 6 THEN column_name
    ->       ELSE NULL END) col6,
    ->     max(CASE WHEN ordinal_position = 7 THEN column_name
    ->       ELSE NULL END) col7,
    ->     max(CASE WHEN ordinal_position = 8 THEN column_name
    ->       ELSE NULL END) col8,
    ->     max(CASE WHEN ordinal_position = 9 THEN column_name
    ->       ELSE NULL END) col9
    ->   FROM information_schema.columns
    ->   WHERE table_schema = 'bank' AND table_name = 'product'
    ->   GROUP BY table_name
    ->  ) cols;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @qry;
+-------------------------------------------------------------------------------
---------------------+
| @qry
                     |
+-------------------------------------------------------------------------------
---------------------+
| SELECT product_cd,name,product_type_cd,date_offered,date_retired FROM product
WHERE product_cd = ? |
+-------------------------------------------------------------------------------
---------------------+
1 row in set (0.00 sec)

mysql> PREPARE dynsql3 FROM @qry;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> SET @prodcd = 'MM';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql3 USING @prodcd;
+------------+----------------------+-----------------+--------------+--------------+
| product_cd | name                 | product_type_cd | date_offered | date_retired |
+------------+----------------------+-----------------+--------------+--------------+
| MM         | money market account | ACCOUNT         | 2004-01-01   | NULL         |
+------------+----------------------+-----------------+--------------+--------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE dynsql3;
Query OK, 0 rows affected (0.00 sec)

The query pivots the first nine columns in the product table, builds a query string using the concat and concat_ws functions, and assigns the string to the qry variable. The query string is then executed as before.

Write a query that lists all of the indexes in the bank schema. Include the table names.

Write a query that generates output that can be used to create all of the indexes on the bank.employee table. Output should be of the form:

"ALTER TABLE <table_name> ADD INDEX <index_name> (<column_list>)"