To view objects in MySQL

Table:

Select Table_schema,table_name,table_type,engine from Information_schema.tables where Table_schema not in (' Performance_schema ', ' information_schema ', ' MySQL ');

View:

Select Table_schema,table_name from information_schema.tables where table_type= ' view ';

Index:

SELECT Table_schema,table_name,index_name,index_type from INFORMATION_SCHEMA. STATISTICS;

Stored procedures:

Select Db,name from Mysql.proc;

Function:

SELECT * FROM Mysql.func

Event:

Select Db,name from Mysql.event;

Trigger:

Select Trigger_schema,trigger_name from Information_schema. ' TRIGGERS ';

Constraints

Select Table_schema,table_name,constraint_name,constraint_type from Information_schema. ' Table_constraints ';

FOREIGN key:

Select Constraint_schema, TABLE_NAME, constraint_name, referenced_table_name from ' Information_schema '. referential_constraints;

Determine if all tables have a primary key:

SELECT

Table_schema,table_name

From

Information_schema. TABLES

WHERE

Table_schema not in (' Information_schema ', ' performance_schema ', ' sys ', ' MySQL ') and

TABLE_NAME Not IN (

SELECT

table_name

From

Information_schema.table_constraints T

JOIN information_schema.key_column_usage k USING (

Constraint_name,

Table_schema,

table_name

)

WHERE

T.constraint_type = ' PRIMARY KEY '

and T.table_schema not in (' Information_schema ', ' performance_schema ', ' sys ', ' MySQL ')

);

Count the amount of data in a MySQL database

SELECT Table_schema,table_name,engine,table_rows,avg_row_length,concat (ROUND (data_length/1024/1024/1024,2), ' G ') "Data_length", Concat (ROUND (index_length/1024/1024/1024,2), ' G ') "Index_length", Concat (ROUND (index_length/1024/ 1024/1024) + (data_length/1024/1024/1024), 2), ' G ') "Total", table_collation,create_time from

Information_schema. TABLES WHERE table_schema = ' xxx ' ORDER by table_rows DESC;

To view objects in MySQL