DBA_OBJECTS View for MySQL

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;

Enjoy!

Augusto Bott

Want to talk with an expert? Schedule a call with our team to get the conversation started.

6 Comments. Leave new

Roland Bouman

February 17, 2009 3:21 pm

Hi there!

this is a nice idea but I think your implementation is flawed:

#1
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)

#2
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?

#3
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’

#4
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.

#5
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

#6
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

#7
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 (
OBJECT_SCHEMA
, OBJECT_TYPE
, OBJECT_NAME
, OBJECT_COMMENT
, SUPER_OBJECT_SCHEMA
, SUPER_OBJECT_TYPE
, SUPER_OBJECT_NAME
)
AS
SELECT SCHEMA_NAME
, ‘SCHEMA’
, SCHEMA_NAME
, null
, null
, null
, null
FROM information_schema.SCHEMATA
UNION ALL
SELECT TABLE_SCHEMA
, CONCAT(TABLE_TYPE, IF(TABLE_TYPE=’BASE TABLE’, CONCAT(‘ (‘, ENGINE, ‘)’), ”))
, TABLE_NAME
, TABLE_COMMENT
, TABLE_SCHEMA
, ‘SCHEMA’
, TABLE_SCHEMA
FROM information_schema.TABLES
UNION ALL
SELECT CONSTRAINT_SCHEMA
, CONCAT(CONSTRAINT_TYPE, ‘ CONSTRAINT’)
, CONSTRAINT_NAME
, null
, TABLE_SCHEMA
, ‘BASE TABLE’
, TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS
UNION ALL
SELECT INDEX_SCHEMA
, CONCAT(IF(NON_UNIQUE, ”, ‘UNIQUE ‘), INDEX_TYPE, ‘ ‘, ‘INDEX’)
, INDEX_NAME
, COMMENT
, TABLE_SCHEMA
, ‘BASE TABLE’
, TABLE_NAME
FROM information_schema.STATISTICS
WHERE SEQ_IN_INDEX = 1 — report each index only once
UNION ALL
SELECT null
, CONCAT(PLUGIN_TYPE, ‘ PLUGIN’)
, PLUGIN_NAME
, PLUGIN_DESCRIPTION
, null
, ‘library’
, PLUGIN_LIBRARY
FROM information_schema.PLUGINS
UNION ALL
SELECT EVENT_SCHEMA
, ‘EVENT’
, EVENT_NAME
, EVENT_COMMENT
, EVENT_SCHEMA
, ‘SCHEMA’
, EVENT_SCHEMA
FROM information_schema.EVENTS
UNION ALL
SELECT TRIGGER_SCHEMA
, CONCAT(ACTION_TIMING, ‘ ‘, EVENT_MANIPULATION, ‘ ‘, ACTION_ORIENTATION, ‘ TRIGGER’)
, TRIGGER_NAME
, null
, EVENT_OBJECT_SCHEMA
, ‘BASE TABLE’
, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
UNION ALL
SELECT ROUTINE_SCHEMA
, ROUTINE_TYPE
, ROUTINE_NAME
, ROUTINE_COMMENT
, ROUTINE_SCHEMA
, ‘SCHEMA’
, ROUTINE_SCHEMA
FROM information_schema.ROUTINES

Caveats:

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.

kind regards,

Roland BOuman