mysql查看所有表的详细信息和注释（表、视图、函数等），简要查看表字段信息describe、show full columns。查看对象DDL语句、建表语句

1.总结

1.1.查看所有对象的详细信息和注释（表、视图、函数等）

-- 1.表、视图、字段的详细信息、注释说明
-- 1.1.表、视图
select * from information_schema.tables  where table_schema in ('test') order by table_schema,table_type,table_name;
select * from information_schema.views   where table_schema in ('test') order by table_schema,table_name;
-- 1.2.字段
select * from information_schema.columns where table_schema in ('test') order by table_schema,table_name,ordinal_position;
-- 2.表、视图的字段简要信息
-- 2.1.超级简要
describe student;
describe student_view;
-- 2.2.简要。比describe多点
show full columns from student;
show full columns from student_view;
-- 3.函数、存储过程
select * from information_schema.routines where routine_schema in ('test') order by routine_schema,routine_type,routine_name;

1.2.查看所有对象DDL定义语句（表、视图、函数等）

-- 1.2.查看所有对象DDL定义语句（表、视图、函数等）
show create table      student;
show create view       student_view;
show create function   get_stu_name;
show create procedure  my_procedure;
show create database   test;
show create tablespace ***;
show create user       root;

2.测试

建表、视图、索引

drop table if exists student;
create table student
(
    s_id   int          default null comment '学生学号',
    s_name varchar(100) default null comment '学生姓名',
    key idx_s_name (s_name)
) comment ='学生';

-- 视图
create or replace view student_view as
select s_id, s_name
from student
order by s_id desc;

-- 函数
drop function if exists get_stu_name;
create function get_stu_name(p_s_id int)
    returns varchar(100)
begin
    return (select s_name from student where s_id = p_s_id);
end;

-- 存储过程
drop procedure if exists my_procedure;
create procedure my_procedure()
begin
    #routine body goes here...
end;

2.1.查看所有对象的详细信息和注释（表、视图、函数等）

2.1.1.表、视图、字段的详细信息和注释

-- 1.1.表、视图
select * from information_schema.tables  where table_schema in ('test') order by table_schema,table_type,table_name;
select * from information_schema.views   where table_schema in ('test') order by table_schema,table_name;
-- 1.2.字段
select * from information_schema.columns where table_schema in ('test') order by table_schema,table_name,ordinal_position;

在这里插入图片描述

2.1.2.表、视图的字段简要信息

-- 2.表、视图的字段简要信息
-- 2.1.超级简要
describe student;
describe student_view;
-- 2.2.简要。比describe多显示几个字段
show full columns from student;
show full columns from student_view;

在这里插入图片描述

2.1.3.函数、存储过程、触发器等

-- 3.函数、存储过程
select * from information_schema.routines where routine_schema in ('test') order by routine_schema,routine_type,routine_name;

在这里插入图片描述

2.1.4.查看所有对象DDL定义语句（表、视图、函数等）

-- 1.2.查看所有对象DDL定义语句（表、视图、函数等）
show create table      student;
show create view       student_view;
show create function   get_stu_name;
show create procedure  my_procedure;
show create database   test;
show create tablespace ***;
show create user       root;