Search code examples
mysqlsqlmariadbinformation-schema

List of all objects of data base

I need a unique table in MariaDB with the list of all objects in a DB.

In MS SQL Server I use only one table (sys.Objects) that have all objects and have a column 'TYPE' with the type of Object, for example: FUNCTION, TRIGGER, TABLE, PROCEDURE...

In MariaDB/MySQL I know tables INFORMATION_SCHEMA.Tables, views, triggers,..., but somebody know a unique table with all objects?

Solution

  • INFORMATION_SCHEMA doesn't provide information for all objects.

    Since PERFORMANCE_SCHEMA.objects_summary_global_by_type doesn't contain all object types (and usually PERFORMANCE_SCHEMA isn't enabled by default) you could create your own view:

    CREATE VIEW objects AS 
  select ROUTINE_NAME AS `object`,
         ROUTINE_SCHEMA AS `schema`,
         ROUTINE_TYPE AS `object_type`
         from information_schema.routines 
  union select TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE
         from information_schema.tables
  union select TRIGGER_NAME, TRIGGER_SCHEMA, 'TRIGGER'
         from information_schema.triggers
  union select EVENT_NAME, EVENT_SCHEMA, 'EVENT'
         from information_schema.events
  union select INDEX_NAME, INDEX_SCHEMA, concat('INDEX (',INDEX_TYPE,')')
         from information_schema.statistics

    This view is based on sys.schema_object_overview which was introduced in MariaDB 10.6