Mysql表信息查询

参考博客：https://www.cnblogs.com/sumlen/p/11101315.html

select * from dike_register.tb_area;

-- 查询所有的表名和列名
SELECT TABLE_NAME	,column_name	
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SCHEMA_NAME;

-- 查询表的字段、列名和字段类型
select TABLE_NAME , TABLE_COMMENT ,column_name,data_type,column_type ,COLUMN_COMMENT from information_schema.COLUMNS where table_schema = SCHEMA_NAMEand table_name = TABLE_NAME;

-- 查询表的注释

SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = TABLE_NAME AND TABLE_SCHEMA = SCHEMA_NAME;


-- 查看所有的表名称、注释、字段信息
select rs1.TABLE_NAME,
       rs3.TABLE_COMMENT,
			 rs2.column_name,
			 rs2.data_type,
			 rs2.column_type
			 
from
(
SELECT  distinct TABLE_NAME	
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_SCHEMA = SCHEMA_NAME
) rs1 
left join 

(
select TABLE_NAME , 
			 column_name,
			 data_type,
			 column_type ,
			 COLUMN_COMMENT 
from information_schema.COLUMNS 
where table_schema = SCHEMA_NAME

)rs2 on rs1.TABLE_NAME = rs2.TABLE_NAME
left join 
(
SELECT TABLE_COMMENT ,table_name
FROM INFORMATION_SCHEMA.TABLES  
WHERE  TABLE_SCHEMA = SCHEMA_NAME
) rs3 
on rs1.TABLE_NAME = rs3.TABLE_NAME;