When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the
information_schema database, but it’s scattered through several different tables.
Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.
In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.
CREATE DATABASE IF NOT EXISTS dba; USE dba; DROP PROCEDURE IF EXISTS `dba`.`get_objects`; DELIMITER $$ CREATE DEFINER=CURRENT_USER PROCEDURE `dba`.`get_objects`() LANGUAGE SQL DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER COMMENT 'populates a temporary table with a collection of objects across the database' BEGIN DECLARE record_not_found INTEGER DEFAULT 0; DECLARE schema_name VARCHAR(64) character set utf8; DECLARE my_schema CURSOR FOR SHOW DATABASES; DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1; DROP TEMPORARY TABLE IF EXISTS dba.dba_objects; CREATE TEMPORARY TABLE `dba`.`dba_objects` ( `OBJECT_NAME` varchar(64) character set utf8 NOT NULL default '', `SUPER_OBJECT` varchar(64) character set utf8, `OBJECT_TYPE` varchar(64) NOT NULL default '', `SUPER_OBJECT_TYPE` varchar(10), `SCHEMA_NAME` varchar(64) character set utf8 ) ENGINE=MyISAM; OPEN my_schema; allSchemas: LOOP FETCH my_schema INTO schema_name; IF record_not_found THEN LEAVE allSchemas; END IF; INSERT INTO `dba`.`dba_objects` (`OBJECT_NAME`, `SUPER_OBJECT`, `OBJECT_TYPE`, `SUPER_OBJECT_TYPE`, `SCHEMA_NAME`) VALUES (schema_name,'sys', 'schema',null,null); END LOOP AllSchemas; CLOSE my_schema; INSERT INTO `dba`.`dba_objects` (`OBJECT_NAME`, `SUPER_OBJECT`, `OBJECT_TYPE`, `SUPER_OBJECT_TYPE`, `SCHEMA_NAME`) SELECT * FROM ( SELECT TABLE_NAME, TABLE_SCHEMA, 'table', 'schema', TABLE_SCHEMA AS s1 FROM information_schema.tables UNION SELECT TABLE_NAME, TABLE_SCHEMA, 'view', 'schema', TABLE_SCHEMA AS s1 FROM information_schema.VIEWS UNION SELECT COLUMN_NAME, TABLE_NAME, 'column', 'table', TABLE_SCHEMA FROM information_schema.COLUMNS UNION SELECT CONSTRAINT_NAME, TABLE_NAME, 'index', 'table', TABLE_SCHEMA FROM information_schema.KEY_COLUMN_USAGE ) a; END$$ DELIMITER ;
Here’s an example on how to use it:
CALL dba.get_objects; SELECT * FROM dba.dba_objects;
this is a nice idea but I think your implementation is flawed:
the cursor really isn’t necessary. You can get all this information using the information_schema.SCHEMATA system view. Also, a CURSOR on any SHOW statement is not supported (yes, I know it works…sometimes)
You could simply create a view on these information schema tables instead of filling a temporary table. Although a view on the I_S tables will perform quite nicely provided you scan it once completely before using it, I can see some benefit of a using a table – however, your table is completely without indexes and does not even have a primary key. Really, if you are taking the trouble, why not create a nicely indexed structure?
Currently, you are reporting VIEWS twice, once as table and once as view because you are doing separate queries on information_schema.TABLES and information_Schema.VIEWS. This is wrong and not necessary – you can simply query only information_schema.TABLES and use TABLE_TYPE instead of the constants ‘table’ and ‘view’. As a bonus, you will correctly report information_schema tables as ‘SYSTEM VIEW’
I don’t think you are using KEY_COLUMN_USAGE correctly.I mean, this table contains one row for each *column* used in either a PRIMARY KEY constraint or FOREIGN KEY constraint. From your query the intention seems to be to report *indexes*. If you want indexes, you should do a GROUP BY on table_schema, table_name, index_name on the information_schema.STATISTICS table.
You are using a UNION. If you fix #4, you can write the legs of the UNION query so that you only yield unique objects, and hence you could use UNION ALL which should matter performance-wise
You are not reporting all objects. I don’t see plugins, triggers, stored procedures and events. These can all be fetched from the information_schema
if you are willing to include a dependency on the mysql database, you can even include UDFs.
Now, I know it’s easy to bitch and all, so now I will contribute what I would feel more comfortable with:
CREATE OR REPLACE VIEW dba_objects (
, CONCAT(TABLE_TYPE, IF(TABLE_TYPE=’BASE TABLE’, CONCAT(‘ (‘, ENGINE, ‘)’), ”))
, CONCAT(CONSTRAINT_TYPE, ‘ CONSTRAINT’)
, ‘BASE TABLE’
, CONCAT(IF(NON_UNIQUE, ”, ‘UNIQUE ‘), INDEX_TYPE, ‘ ‘, ‘INDEX’)
, ‘BASE TABLE’
WHERE SEQ_IN_INDEX = 1 — report each index only once
, CONCAT(PLUGIN_TYPE, ‘ PLUGIN’)
, CONCAT(ACTION_TIMING, ‘ ‘, EVENT_MANIPULATION, ‘ ‘, ACTION_ORIENTATION, ‘ TRIGGER’)
, ‘BASE TABLE’
1) indexes and constraints are reported as separate objects – in MySQL there is physically no difference between f.e. a UNIQUE CONSTRAINT and the index that implements it – this view however reports them as separate objects.
2) this is a view on the information schema – it can be slow on the first run or when there are many many objects in the information schema
3) I don’t include columns. It clutters he output and IMO they are not objects in themselves.
4) SUPER_OBJECT_SCHEMA always reports a SCHEMA if possible, even if the super object is itself a schema.
5) I excluded the user-defined functions to avoid a dependency on the mysql database.
Thanks for you feedback – it’s much appreciated! I’ll make sure we include the improvements you suggested in future posts/snippets.
Updated the snippet on the MySQL Forge – ditched the
PROC. A newer version will be posted soon.
Brilliant idea. Makes Oracle DBA’s life so much easier in MySQL world. :)
[…] DBA_OBJECTS para MySQL, en The Pythian Group. (en […]