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!