-- as per Roland Boumann's suggestions, a new version -- TODO: add triggers, procedures
CREATE DATABASE IF NOT EXISTS dba; USE dba;
DROP VIEW IF EXISTS `dba`.`dba_objects`; CREATE VIEW `dba`.`dba_objects` AS SELECT SCHEMA_NAME AS `OBJECT_NAME`, 'system' AS `SUPER_OBJECT`, 'schema' AS `OBJECT_TYPE`, 'system' AS `SUPER_OBJECT_TYPE`, SCHEMA_NAME as `SCHEMA_NAME` FROM information_schema.schemata UNION 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;
SELECT * FROM dba.dba_objects;
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. Well, we do have information like that on MySQL 5.0 (and up) thru the information_schema database, but it’s scattered thru several different tables. This 'view' allow us to quickly locate any objects within a MySQL database. It's actually a stored procedure, but after the table gets populated, you can query it as any other table.
CODE EXAMPLE: DBA_OBJECTS view for MySQL 5.0
-- TODO: add triggers, procedures
CREATE DATABASE IF NOT EXISTS dba;
USE dba;
DROP VIEW IF EXISTS `dba`.`dba_objects`;
CREATE VIEW `dba`.`dba_objects` AS
SELECT SCHEMA_NAME AS `OBJECT_NAME`, 'system' AS `SUPER_OBJECT`, 'schema' AS `OBJECT_TYPE`, 'system' AS `SUPER_OBJECT_TYPE`, SCHEMA_NAME as `SCHEMA_NAME` FROM information_schema.schemata
UNION
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;
SELECT * FROM dba.dba_objects;
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. Well, we do have information like that on MySQL 5.0 (and up) thru the information_schema database, but it’s scattered thru several different tables. This 'view' allow us to quickly locate any objects within a MySQL database. It's actually a stored procedure, but after the table gets populated, you can query it as any other table.