Accessing Metadata through Stored Routines

Accessing metadata can be optimized by using stored routines.  Stored routines provide the ability to filter the data in a more useful way.  For example, when I'm looking at table data I usually want to look at the index information also.  So I use a stored routine called tabinfo that gives me key information I need for tables and indexes.

-- Create the tabinfo stored procedure.

DROP PROCEDURE IF EXISTS  tabinfo;

CREATE PROCEDURE tabinfo(ptableschema  VARCHAR(30))

SELECT  t.table_name, engine, table_rows,

                i.column_name, i.index_name, i.cardinality

FROM     information_schema.tables t, information_schema.statistics i

WHERE  t.table_name = i.table_name

      AND  t.table_schema = i.table_schema

      AND  t.table_schema = ptableschema

ORDER BY t.table_rows DESC;

-- Execute the tabinfo stored routine using the CALL command.

mysql> CALL tabinfo('world');