Mysql 查询数据库表、视图、函数、存储过程、触发器...信息

一、查询数据库所有表、视图、索引、函数、存储过程、触发器名

--查询数据库sakila所有表

select
	table_name
from
	information_schema.tables
where
	table_schema = 'sakila'
	and table_type = 'BASE TABLE'

--查询数据库sakila所有视图

select
	TABLE_NAME as VIEW_NAME
from
	INFORMATION_SCHEMA.VIEWS
where
	TABLE_SCHEMA = 'sakila'


--查询数据库sakila所有索引
select
	s.TABLE_NAME ,
	s.INDEX_NAME 
from
	information_schema.STATISTICS s
where
	s.TABLE_SCHEMA = 'sakila'

--查询数据库sakila所有函数
select
	ROUTINE_NAME 
from
	information_schema.ROUTINES
where
	ROUTINE_SCHEMA = 'sakila'
	and ROUTINE_TYPE = 'FUNCTION'

--查询数据库sakila所有存储过程

	select
	ROUTINE_NAME 
from
	information_schema.ROUTINES
where
	ROUTINE_SCHEMA = 'sakila'
	and ROUTINE_TYPE = 'PROCEDURE'

--查询数据库sakila所有触发器
select
	TRIGGER_NAME as object_name
from
	INFORMATION_SCHEMA.TRIGGERS
where
	TRIGGER_SCHEMA = 'sakila'
order by
	TRIGGER_SCHEMA,
	TRIGGER_NAME

二、查询表详细信息

--查询表的列信息

select
	COL.COLUMN_NAME as '列名',
	COL.ORDINAL_POSITION as '#',
	COL.COLUMN_TYPE as '数据类型',
	COL.IS_NULLABLE as '非空',
	COL.COLUMN_KEY as '键',
	COL.COLUMN_DEFAULT as '默认',
	COL.EXTRA as '额外的',
	COL.GENERATION_EXPRESSION as '表达式',
	COL.COLUMN_COMMENT as '注释'
from
	information_schema.COLUMNS COL
where
	TABLE_SCHEMA = 'sakila'
	and TABLE_NAME = 'rental'

--查询表的约束信息（除外键约束），检查表达式  MySQL 8.0.16 及之后的版本才有
select
	tc.CONSTRAINT_NAME as '名称',
	kcu.COLUMN_NAME as '列',
	tc.CONSTRAINT_TYPE as '约束类型',
	tc.TABLE_NAME as '所有者',
	cc.CHECK_CLAUSE as '检查表达式'
from
	information_schema.TABLE_CONSTRAINTS tc,
	information_schema.KEY_COLUMN_USAGE kcu
left join
 information_schema.CHECK_CONSTRAINTS cc on
	(kcu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME)
where
	tc.TABLE_SCHEMA = 'sakila'
	and tc.TABLE_NAME = 'rental'
	and tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
	and tc.TABLE_NAME = kcu.TABLE_NAME
	and tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
	and tc.CONSTRAINT_TYPE != 'FOREIGN KEY'

--查询表的外键信息
select
	rc.CONSTRAINT_NAME as '名称',
	kcu.COLUMN_NAME as '列',
	kcu.TABLE_NAME as '所有者',
	rc.REFERENCED_TABLE_NAME as '引用表',
	tc.CONSTRAINT_TYPE as '类型',
	rc.UNIQUE_CONSTRAINT_NAME as '引用对象',
	rc.DELETE_RULE as '删除时的规则',
	rc.UPDATE_RULE as '更新时的规则'
from
	information_schema.REFERENTIAL_CONSTRAINTS rc ,
	information_schema.TABLE_CONSTRAINTS tc,
	information_schema.KEY_COLUMN_USAGE kcu
where
	rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	and kcu .TABLE_NAME = rc.TABLE_NAME
	and rc.TABLE_NAME = tc.TABLE_NAME
	and tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
	and kcu.TABLE_SCHEMA = 'sakila'
	and kcu.TABLE_NAME = 'rental'

--查询表的触发器
select
	T.TRIGGER_NAME as '触发器名称',
	T.ACTION_TIMING as '触发时机(Timing)',
	T.EVENT_MANIPULATION as '类型',
	T.EVENT_OBJECT_TABLE as '表',
	T.ACTION_STATEMENT as '触发器主体'
from
	information_schema.triggers T
where
	event_object_schema = 'sakila'
	and event_object_table = 'rental'

--查询表的索引
select
	S.INDEX_NAME as '索引名',
	S.COLUMN_NAME as '列',
	S.TABLE_NAME as '表',
	S.INDEX_TYPE as '索引类型',
	S.COLLATION  as '升序',
	S.NULLABLE as '可为空',
	S.NON_UNIQUE as '唯一的',
	C.EXTRA  as '额外的',
	S.CARDINALITY as '基数',
	S.COMMENT as '注释'
from
	information_schema.STATISTICS S,
	information_schema.COLUMNS C
where
	S.TABLE_SCHEMA = 'sakila'
	and S.TABLE_NAME = 'rental'
	and S.TABLE_SCHEMA = C.TABLE_SCHEMA 
	and S.TABLE_NAME = C.TABLE_NAME 
	and S.COLUMN_NAME = C.COLUMN_NAME

--查询表分区
select
	P.PARTITION_NAME as '分区名称',
	P.PARTITION_ORDINAL_POSITION as '位置',
	P.TABLE_NAME as '表',
	P.SUBPARTITION_METHOD as '方法',
	P.PARTITION_EXPRESSION as '表达式',
	P.PARTITION_DESCRIPTION as '描述',
	P.TABLE_ROWS as '表行',
	P.AVG_ROW_LENGTH as '平均长度',
	P.DATA_LENGTH as '数据长度',
	P.MAX_DATA_LENGTH as '最大数据长度',
	P.INDEX_LENGTH as '索引长度',
	P.DATA_FREE as '已分配但未使用的数据空间大小',
	P.CHECKSUM as '核对合计',
	P.PARTITION_COMMENT as '注释',
	P.NODEGROUP as '节点组'
from
	information_schema.PARTITIONS P
where
	TABLE_SCHEMA = 'sakila'
	and TABLE_NAME = 'rental'

三、创建语句查询

表：show create table sakila.rental ;

视图：show create view sakila.actor_info;

函数：show create function sakila.get_customer_balance ;

存储过程：show create procedure sakila.film_in_stock;

触发器：show create trigger sakila.ins_film;

四、查看存储过程、函数的参数信息

--存储过程参数信息（函数的类型为FUNCTION）
select
	p.PARAMETER_MODE ,
	p.PARAMETER_NAME ,
	p.ORDINAL_POSITION ,
	p.DATA_TYPE ,
	p.NUMERIC_PRECISION
from
	information_schema.PARAMETERS p
where
	p.SPECIFIC_SCHEMA = 'sakila'
	and p.SPECIFIC_NAME = 'film_in_stock'
	and p.ROUTINE_TYPE = 'PROCEDURE'