MySQL Tips : Verify database Objects

Verify database Objects:

1. Verify table.
a. SHOW TABLE STATUS LIKE '%user%'\G
b. SHOW TABLE STATUS from forum LIKE '%user%' \G

2. It will show you all tables have MyISAM engine.
a. SELECT TABLE_SCHEMA, table_name, table_type, engine From information_schema.tables where engine='MyISAM';

3. To know rows per table, you can use a query like this:
a. SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'forum';

4. Verify you procedure / function.
a. SHOW procedure/FUNCTION STATUS LIKE ´hello´G
b. show create procedure/function hello\G

5. Routines
a. select routine_name from information_schema.routines; // list of all the routines in the system
b. select routine_name, routine_schema, routine_type from information_schema.routines; // This lists all of the routines in the system. With additional Information like the database the routines belongs too and also distinct between the different routines.
c. select routine_name, routine_schema, routine_type from information_schema.routines where routine_schema = database() // This time we can see just the routines for the currently selected database.


6. Triggers
a. select TRIGGER_SCHEMA,TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;
b. select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = database();