Close

Database Information Schema

[Updated: Sep 10, 2018, Created: Mar 20, 2017]

Database 

This page shows useful queries of the information_schema (ANSI-standard). The examples output is taken from H2 database.

Tables

To list all tables

SELECT * FROM INFORMATION_SCHEMA.TABLES
[TEST, INFORMATION_SCHEMA, FUNCTION_COLUMNS, SYSTEM TABLE, CACHED, null, , 24, -22, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, CONSTANTS, SYSTEM TABLE, CACHED, null, , 24, -23, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, SEQUENCES, SYSTEM TABLE, CACHED, null, , 24, -9, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, RIGHTS, SYSTEM TABLE, CACHED, null, , 24, -12, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, TRIGGERS, SYSTEM TABLE, CACHED, null, , 24, -25, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, CATALOGS, SYSTEM TABLE, CACHED, null, , 24, -6, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, CROSS_REFERENCES, SYSTEM TABLE, CACHED, null, , 24, -20, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, SETTINGS, SYSTEM TABLE, CACHED, null, , 9223372036854775807, -7, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, FUNCTION_ALIASES, SYSTEM TABLE, CACHED, null, , 24, -13, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, VIEWS, SYSTEM TABLE, CACHED, null, , 24, -18, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, TYPE_INFO, SYSTEM TABLE, CACHED, null, , 24, -5, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, CONSTRAINTS, SYSTEM TABLE, CACHED, null, , 24, -21, null, org.h2.table.MetaTable, 1000]
[TEST, INFORMATION_SCHEMA, COLUMNS, SYSTEM TABLE, CACHED, null, , 24, -2, null, org.h2.table.MetaTable, 1000]
 .........

Above query shows following columns:

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, STORAGE_TYPE, SQL, REMARKS, LAST_MODIFICATION, ID, TYPE_NAME, TABLE_CLASS, ROW_COUNT_ESTIMATE

We can also find column names from COLUMNS table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'TABLES'
[TEST, INFORMATION_SCHEMA, TABLES, TABLE_CATALOG, 1, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, TABLE_SCHEMA, 2, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, TABLE_NAME, 3, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, TABLE_TYPE, 4, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, STORAGE_TYPE, 5, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, SQL, 6, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, REMARKS, 7, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, LAST_MODIFICATION, 8, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, ID, 9, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, TYPE_NAME, 10, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, TABLE_CLASS, 11, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, TABLES, ROW_COUNT_ESTIMATE, 12, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]

To find above output columns names:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'COLUMNS'
[TEST, INFORMATION_SCHEMA, COLUMNS, TABLE_CATALOG, 1, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, TABLE_SCHEMA, 2, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, TABLE_NAME, 3, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, COLUMN_NAME, 4, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, ORDINAL_POSITION, 5, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, COLUMN_DEFAULT, 6, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, IS_NULLABLE, 7, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, DATA_TYPE, 8, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, CHARACTER_MAXIMUM_LENGTH, 9, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, CHARACTER_OCTET_LENGTH, 10, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, NUMERIC_PRECISION, 11, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, NUMERIC_PRECISION_RADIX, 12, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, NUMERIC_SCALE, 13, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, CHARACTER_SET_NAME, 14, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, COLLATION_NAME, 15, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, TYPE_NAME, 16, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, NULLABLE, 17, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, IS_COMPUTED, 18, null, YES, 16, 1, 1, 1, 10, 0, Unicode, OFF, BOOLEAN, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, SELECTIVITY, 19, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, CHECK_CONSTRAINT, 20, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, SEQUENCE_NAME, 21, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, REMARKS, 22, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, COLUMNS, SOURCE_DATA_TYPE, 23, null, YES, 5, 5, 5, 5, 10, 0, Unicode, OFF, SMALLINT, 1, false, 50, , null, , null]

To view only user defined tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'

Sequences

This will list all sequences:

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SEQUENCES'
[TEST, INFORMATION_SCHEMA, SEQUENCES, SEQUENCE_CATALOG, 1, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, SEQUENCE_SCHEMA, 2, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, SEQUENCE_NAME, 3, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, CURRENT_VALUE, 4, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, INCREMENT, 5, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, IS_GENERATED, 6, null, YES, 16, 1, 1, 1, 10, 0, Unicode, OFF, BOOLEAN, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, REMARKS, 7, null, YES, 12, 2147483647, 2147483647, 2147483647, 10, 0, Unicode, OFF, VARCHAR, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, CACHE, 8, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, MIN_VALUE, 9, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, MAX_VALUE, 10, null, YES, -5, 19, 19, 19, 10, 0, Unicode, OFF, BIGINT, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, IS_CYCLE, 11, null, YES, 16, 1, 1, 1, 10, 0, Unicode, OFF, BOOLEAN, 1, false, 50, , null, , null]
[TEST, INFORMATION_SCHEMA, SEQUENCES, ID, 12, null, YES, 4, 10, 10, 10, 10, 0, Unicode, OFF, INTEGER, 1, false, 50, , null, , null]

Procedures and Functions (ROUTINES)

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
[PUBLIC, SYSTEM_LOBS, ALLOC_BLOCKS_10070, PUBLIC, SYSTEM_LOBS, ALLOC_BLOCKS, PROCEDURE, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, SQL, CREATE PROCEDURE SYSTEM_LOBS.ALLOC_BLOCKS(IN B_COUNT INTEGER,IN B_OFFSET INTEGER,IN L_ID BIGINT) SPECIFIC ALLOC_BLOCKS_10070 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE LOB_ADDR INT DEFAULT NULL;DECLARE REMAINING_COUNT INT DEFAULT 0;DECLARE BL_ADDR INT DEFAULT NULL;DECLARE TEMP_COUNT INT DEFAULT 0;DECLARE BL_OFFSET INT DEFAULT 0;SET REMAINING_COUNT=B_COUNT;SET BL_OFFSET=B_OFFSET;MAIN_LOOP:LOOP SET BL_ADDR=(SELECT BLOCK_ADDR FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT=REMAINING_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF BL_ADDR IS NOT NULL THEN CALL CONVERT_BLOCK(BL_ADDR,REMAINING_COUNT,BL_OFFSET,L_ID);IF LOB_ADDR IS NULL THEN SET LOB_ADDR=BL_ADDR;END IF;LEAVE MAIN_LOOP;END IF;SET(BL_ADDR,TEMP_COUNT)=(SELECT BLOCK_ADDR,BLOCK_COUNT FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT<REMAINING_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF BL_ADDR IS NOT NULL THEN CALL CONVERT_BLOCK(BL_ADDR,TEMP_COUNT,BL_OFFSET,L_ID);IF LOB_ADDR IS NULL THEN SET LOB_ADDR=BL_ADDR;END IF;SET REMAINING_COUNT=REMAINING_COUNT-TEMP_COUNT;SET BL_OFFSET=BL_OFFSET+TEMP_COUNT;SET BL_ADDR=NULL;SET TEMP_COUNT=0;ELSE CALL CREATE_EMPTY_BLOCK(BL_ADDR,REMAINING_COUNT);CALL CONVERT_BLOCK(BL_ADDR,REMAINING_COUNT,BL_OFFSET,L_ID);IF LOB_ADDR IS NULL THEN SET LOB_ADDR=BL_ADDR;END IF;LEAVE MAIN_LOOP;END IF;END LOOP MAIN_LOOP;END, null, null, null, NO, MODIFIES SQL DATA, null, null, YES, 0, null, null, DEFINER, null, null, null, null, null, null, YES, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null]
[PUBLIC, SYSTEM_LOBS, ALLOC_SINGLE_BLOCK_10073, PUBLIC, SYSTEM_LOBS, ALLOC_SINGLE_BLOCK, PROCEDURE, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, SQL, CREATE PROCEDURE SYSTEM_LOBS.ALLOC_SINGLE_BLOCK(IN B_COUNT INTEGER,IN B_OFFSET INTEGER,IN L_ID BIGINT) SPECIFIC ALLOC_SINGLE_BLOCK_10073 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE BL_ADDR INT DEFAULT NULL;SET BL_ADDR=(SELECT BLOCK_ADDR FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT=B_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF BL_ADDR IS NULL THEN CALL CREATE_EMPTY_BLOCK(BL_ADDR,B_COUNT);END IF;CALL CONVERT_BLOCK(BL_ADDR,B_COUNT,B_OFFSET,L_ID);END, null, null, null, NO, MODIFIES SQL DATA, null, null, YES, 0, null, null, DEFINER, null, null, null, null, null, null, YES, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null]
.............

Columns:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ROUTINES'
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, SPECIFIC_CATALOG, 1, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, SPECIFIC_SCHEMA, 2, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, SPECIFIC_NAME, 3, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, ROUTINE_CATALOG, 4, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, ROUTINE_SCHEMA, 5, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, ROUTINE_NAME, 6, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, ROUTINE_TYPE, 7, null, YES, CHARACTER VARYING, 65536, 131072, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, CHARACTER_DATA, null, null, null, null, null, null, null, VARCHAR(65536), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, MODULE_CATALOG, 8, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, MODULE_SCHEMA, 9, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, MODULE_NAME, 10, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, UDT_CATALOG, 11, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, UDT_SCHEMA, 12, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
[PUBLIC, INFORMATION_SCHEMA, ROUTINES, UDT_NAME, 13, null, YES, CHARACTER VARYING, 128, 256, null, null, null, null, null, null, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_TEXT, PUBLIC, INFORMATION_SCHEMA, SQL_IDENTIFIER, null, null, null, null, null, null, null, VARCHAR(128), null, NO, null, null, null, null, null, null, NEVER, null, NO, CHARACTER VARYING, null, null]
..........

To view only user defined routines:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'PUBLIC'

Others

Similarly we can query other tables and their columns listed in INFORMATION_SCHEMA.TABLES.

See Also