TIDB: 元数据查询语句

一、获取表描述

select table_name,table_comment from information_schema.TABLES where table_schema = '%s' and table_name in (%s)

二、获取视图DDL

SELECT * FROM information_schema.views WHERE TABLE_NAME = '%s' and TABLE_SCHEMA = '%s'

三、判断表是否存在sql

select table_name from information_schema.TABLES where table_name = '%s' and table_schema = '%s'

四、字段详情查询

select table_name,column_name,ordinal_position,column_default,EXTRA,is_nullable,data_type,column_comment,column_key,numeric_precision,numeric_scale,character_maximum_length from information_schema.columns where table_name in ('%s') and table_schema = '%s'

五、查询当前用户是否存在

select host,user form mysql.user where user = '%s'

六、查询库列表

SELECT schema_name SCHEMA_NAME FROM information_schema.schemata

七、查询表列表

select table_name,table_comment from information_schema.TABLES where table_schema = '%s' and table_type = '%s'

八、查询用户对某表所拥有的权限

select Table_priv from `mysql`.`tables_priv` where Host in ('%', '%s') and DB = '%s' and User='%s' and Table_name = '%s'

九、查询分区信息

select * from information_schema.PARTITIONS where table_name in (%s) and table_schema = '%s' ORDER BY TABLE_NAME ASC,PARTITION_ORDINAL_POSITION ASC