• Data About Data
• information_schema
• Working with Metadata
  • Schema Generation Scripts
  • Deployment Verification
  • Dynamic SQL Generation

A database server also needs to store information about all of the database objects (tables, views, indexes, etc.) that were created to store this data in a database. This chapter discusses how and where this information, known as metadata, is stored, how you can access it, and how you can use it to build flexible systems.

Data About Data

Metadata is essentially data about data. Every time you create a database object, the database server needs to record various pieces of information. For example, if you were to create a table with multiple columns, a primary key constraint, three indexes, and a foreign key constraint, the database server would need to store all the following information:

• Table name
• Table storage information (tablespace, initial size, etc.)
• Storage engine
• Column names
• Column data types
• Default column values
• not null column constraints
• Primary key columns
• Primary key name
• Name of primary key index
• Index names
• Index types (B-tree, bitmap)
• Indexed columns
• Index column sort order (ascending or descending)
• Index storage information
• Foreign key name
• Foreign key columns
• Associated table/columns for foreign keys

This data is collectively known as the data dictionary or system catalog. The database server needs to store this data persistently, and it needs to be able to quickly retrieve this data in order to verify and execute SQL statements. Additionally, the database server must safeguard this data so that it can be modified only via an appropriate mechanism, such as the alter table statement.

Every database server uses a different mechanism to publish metadata, such as:

• A set of views, such as Oracle Database’s user_tables and all_constraints views
• A set of system-stored procedures, such as SQL Server’s sp_tables procedure or Oracle Database’s dbms_metadata package
• A special database, such as MySQL’s information_schema database

information_schema

All of the objects available within the information_schema database (or schema, in the case of SQL Server) are views. Unlike the describe utility, the views within information_schema can be queried and, thus, used programmatically.

Working with Metadata

Schema Generation Scripts

Generate a script that will create the various tables, indexes, views, and so on, that the team has deployed. Build a script that will create the sakila.category table. The following codes can be used to create a template-like SQL script.

SELECT 'CREATE TABLE category (' 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 AND extra LIKE 'DEFAULT_GENERATED%' THEN concat(' DEFAULT ',column_default,substr(extra,18)) WHEN extra IS NOT NULL THEN concat(' ', extra)
ELSE '' END, ',') txt
FROM information_schema.columns 
WHERE table_schema = 'sakila' AND table_name = 'category' 
ORDER BY ordinal_position 
) cols 
UNION ALL
SELECT concat(' constraint primary key (') 
FROM information_schema.table_constraints 
WHERE table_schema = 'sakila' AND table_name = 'category' 
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 = 'sakila' AND table_name = 'category' 
AND constraint_name = 'PRIMARY'
ORDER BY ordinal_position 
) cols 
UNION ALL 
SELECT ' )' 
UNION ALL
SELECT ')';

Deployment Verification

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 Sakila schema:

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 = 'sakila' AND tbl.table_type = 'BASE TABLE'
 ORDER BY 1;

Dynamic SQL Generation

Most relational database servers, including SQL Server, Oracle Database, and MySQL, allow SQL statements to be submitted to the server as strings. Submit‐ ting strings to a database engine rather than utilizing its SQL interface is generally known as dynamic SQL execution.

Oracle’s PL/SQL language

execute immediate

SQL Server

sp_executesql

MySQL

prepare, execute, deallocate

SET @qry = 'SELECT customer_id, first_name, last_name FROM customer'; 
PREPARE dynsql1 FROM @qry; 
EXECUTE dynsql1;
DEALLOCATE PREPARE dynsql1;

/*conditions can be specified at runtime*/
SET @qry = 'SELECT customer_id, first_name, last_name FROM customer WHERE customer_id = ?';
PREPARE dynsql2 FROM @qry;
SET @custid = 9;
EXECUTE dynsql2 USING @custid;
SET @custid = 145;
EXECUTE dynsql2 USING @custid;
DEALLOCATE PREPARE dynsql2;

Or you can do the following:

SELECT concat('SELECT ', concat_ws(',', cols.col1, cols.col2),
' FROM customer WHERE customer_id = ?') 
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
FROM information_schema.columns 
WHERE table_schema = 'sakila' AND table_name = 'customer' 
GROUP BY table_name
) cols;

PREPARE dynsql3 FROM @qry; 
SET @custid = 45; Query OK, 0 rows affected (0.00 sec)
EXECUTE dynsql3 USING @custid;
DEALLOCATE PREPARE dynsql3;

Note: Generally, it would be better to generate the query using a procedural language that includes looping constructs, such as Java, PL/SQL, Transact-SQL, or MySQL’s Stored Procedure Language.