The stuff you will find in there:
- Diagram of the information schema
- Slides for the UC presentation, "Grand Tour of the Information Schema"
- script, returns one row for each index (rollup of information_schema.STATISTICS)
- script, lists all redundant indexes. Redundancy rules:
- two indexes with the same columns, type and uniqueness are interchangeable. The one with the largest index name is listed as redundant
- if there is a unique index and a non unique index with the same columns and type exists, the non-unique one is considered redundant
- A non-unique btree index is the leftmost prefix of another btree index (that may or may not be unique)
- stored procedure Detect probles after using @@foreign_key_checks=1. Takes a schema name LIKE pattern and a table name LIKE pattern and reports any foreign keys constraints for which violations exist. TODO: if the referenced table in the foreign key was dropped, the script will encounter a runtime error. Would be nice to properly report instead
- Stored procedure, The Query wizard for the mysql command line pronounced "cues". To use it, run the script, and it starts a new session with the query wizard. Alternatively, do:
USE qwz; CALL qwz(NULL);and follow the on-screen instructions.
- script, Generates HTML documentation for the current schema. Run using a command like:
mysql -uroot -Dsakila -Nrs -e"source schema_doc.sql" > schema_doc.html
- shell, Linux example for running schema_doc.sql
- script, Generates trigger code to emulate foreign keys. Set a schema containing foreign key constraints as the default database. Run the script. The output forms trigger code that may be used to recreate the foreign keys functionality in a database that has the same tables but no declarative foreign keys. For foreign keys with the RESTRICT or NO ACTION rules, a non-existent stored procedure will be called:
p_child_row_exists(). For orphan INSERTs and UPDATEs, a call is made to
p_no_parent_row(). Currently this is the best we can do - it will result in a runtime error and the action causing it will not complete but it is not ideal of course.Corrective rules (CASCADE etc) should just work.
- stored procedure, Creates a federated table (residing on a 5.0 or better remote host)