Thursday, March 19, 2009

MySQL: Query to List Down All Schema Objects


Below is a simple query to ease your day to day work. It simply lists down all the objects of a MySQL Schema. The things that you are mostly interested in e.g. Tables, Views, Routines, Indexes, Triggers and so on.

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.

Update:05/27/2015 - Adding Github Gist for the script


SELECT OBJECT_TYPE
,OBJECT_SCHEMA
,OBJECT_NAME
FROM (
SELECT 'TABLE' AS OBJECT_TYPE
,TABLE_NAME AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLES

UNION

SELECT 'VIEW' AS OBJECT_TYPE
,TABLE_NAME AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.VIEWS

UNION

SELECT 'INDEX[Type:Name:Table]' AS OBJECT_TYPE
,CONCAT (
CONSTRAINT_TYPE
,' : '
,CONSTRAINT_NAME
,' : '
,TABLE_NAME
) AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLE_CONSTRAINTS

UNION

SELECT ROUTINE_TYPE AS OBJECT_TYPE
,ROUTINE_NAME AS OBJECT_NAME
,ROUTINE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.ROUTINES

UNION

SELECT 'TRIGGER[Schema:Object]' AS OBJECT_TYPE
,CONCAT (
TRIGGER_NAME
,' : '
,EVENT_OBJECT_SCHEMA
,' : '
,EVENT_OBJECT_TABLE
) AS OBJECT_NAME
,TRIGGER_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.triggers
) R
WHERE R.OBJECT_SCHEMA = [your-schema-name-here];

3 comments:

  1. Great start, how about adding check constraints, triggers, etc...

    ReplyDelete
  2. Select OBJECT_TYPE,OBJECT_SCHEMA, OBJECT_NAME
    FROM
    (
    Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.TABLES
    Union
    Select 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
    Union
    Select 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTS
    Union
    Select ROUTINE_TYPE as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES
    UNION
    Select 'TRIGGER[Schema:Object]' as OBJECT_TYPE, concat(TRIGGER_NAME,' : ',EVENT_OBJECT_SCHEMA,' : ',EVENT_OBJECT_TABLE ) as OBJECT_NAME, TRIGGER_SCHEMA as OBJECT_SCHEMA from information_schema.triggers
    ) R;

    ReplyDelete