DBA_OBJECTS View for MySQL

Feb 17, 2009 / By Augusto Bott

Tags:

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!

6 Responses to “DBA_OBJECTS View for MySQL”

  • 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

  • Augusto Bott says:

    @Roland,

    Thanks for you feedback – it’s much appreciated! I’ll make sure we include the improvements you suggested in future posts/snippets.

    Cheers!

  • Augusto, thanks!

    kind regards,

    Roland

  • Augusto Bott says:

    Updated the snippet on the MySQL Forge – ditched the PROC. A newer version will be posted soon.

    Cheers!

  • Brilliant idea. Makes Oracle DBA’s life so much easier in MySQL world. :)

  • [...] DBA_OBJECTS para MySQL, en The Pythian Group. (en [...]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>