表：

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where TABLE_SCHEMA not in ('performance_schema','information_schema','mysql');

視圖：

select TABLE_SCHEMA,TABLE_NAME? from information_schema.tables where table_type='view';

索引：

SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS? ;

存儲過程：

select? db,name? from? mysql.proc ;

函數(shù)：

select? ?*? from? mysql.func

事件：

select? db,name? from? mysql.event;

觸發(fā)器：

select TRIGGER_SCHEMA,TRIGGER_NAME from? information_schema.`TRIGGERS`;

約束

select TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from? information_schema.`TABLE_CONSTRAINTS`;

外鍵：

select CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME from `information_schema`.REFERENTIAL_CONSTRAINTS ;

判斷所有的表是否有主鍵：

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')

);

統(tǒng)計(jì)MySQL數(shù)據(jù)庫的數(shù)據(jù)量

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;

總結(jié)

以上是生活随笔為你收集整理的mysql 对象_查看MySQL中的对象的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。