Data About Data

Every time you create a database object, the database server needs to record various pieces of information. This data is collectively known as the data dictionary or system catalog.

• It needs to be persistant, quick and secure.
• It can be modified via alter table statement.

<aside> 💡 Every individual server use different mechanism to publish metadata: Oracle - user_tables and all_constraints views SQL Server - sp_tables procedure MySQL - information_schema database

</aside>

information_schema

All of the objects available within the information_schema database are views.

Unlike describe, the views within information_schema can be queried and used programatically.

select table_name, table_type, table_schema
from information_schema.tables;

select table_name, is_updatable, table_schema
from information_schema.views;

select table_name, column_name,
		character_maximum_length, numeric_scale, numeric_precision
from information_schema.columns
where table_schema = 'sakila'
order by 1;

select constraint_name, table_name, constraint_type
from information_schema.table_constraints
where table_schema = 'sakila'
order by 2;

select *
from information_schema.statistics
where table_schema = 'sakila';
# index

SHOW TABLES FROM INFORMATION_SCHEMA;
# Access various views

Working with Metadata

Schema Generation Scripts (257 Learning SQL)

While some project teams include a full-time database designer who oversees the design and implementation of the database, many projects take the “design-bycommittee” approach, allowing multiple people to create database objects.

While it certainly will be easier to generate the script with the use of a procedural language, querying information_schema views can gives you the ability to generate scripts to create tables, indexes or views.

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.

select tbl.table_name,
	(select count(*) from information_schema.columns as clm
			where clm.table_name = tbl.table_name
			and clm.table_schema = tbl.table_schema) num_columns,
  (select count(*) from information_schema.statistics as sta
			where sta.table_name = tbl.table_name
			and sta.table_schema = tbl.table_schema) num_indexes,
	(select count(*) from information_schema.table_constraints as tc
			where tc.table_name = tbl.table_name
			and tc.table_schema = tbl.table_schema
	    and tc.constraint_type = upper('primary key')) num_primary_keys
from information_schema.tables as tbl
where tbl.table_schema = 'sakila' and tbl.table_type = 'BASE TABLE';

Context: Returning the number of columns, number of indexes, and number of primary key constraints (0 or 1) for each table in the Sakila schema.