【数据库】四大传统数库据元数据查询

一、Mysql

-- 查询列信息
select t.table_catalog,t.table_schema,t.table_name,t.table_type,table_rows
,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,numeric_precision,numeric_scale,datetime_precision,column_type,character_set_name,collation_name
from `information_schema`.`tables` t 
inner join `information_schema`.`columns` c 
on t.table_catalog=c.table_catalog and t.table_schema=c.table_schema and t.table_name=c.table_name
where 1=1
and t.table_schema='test' 
and t.table_name='test_type_meta_basic';


-- 查询索引信息
show index from test.test_type_meta_basic;


-- 查询表的约束
select t.constraint_schema,t.table_name,t.constraint_name,t.constraint_type ,group_concat(c.column_name order by ordinal_position) column_names
from `information_schema`.`table_constraints` t inner join `information_schema`.`key_column_usage` c on 1=1 
and t.table_schema ='test' 
and t.table_name='test_type_meta_basic' 
and t.table_schema =c.table_schema
and t.table_name=c.table_name
and t.constraint_name =c.constraint_name
group by t.constraint_schema,t.table_name,t.constraint_name,t.constraint_type;

Postgres

-- 查询列信息
select t.table_catalog,t.table_schema,t.table_name,t.table_type
,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,numeric_precision,numeric_scale,datetime_precision,udt_name
from "information_schema"."tables" t 
inner join "information_schema"."columns" c 
on t.table_catalog=c.table_catalog and t.table_schema=c.table_schema and t.table_name=c.table_name
where t.table_schema='ischema' and t.table_name='test_type_meta_basic';


-- 查询索引列
select t.tablename,t.indexname,t.indexdef from "pg_catalog"."pg_indexes" t 
where t.schemaname='ischema' and t.tablename ='test_type_meta_basic' 


-- 查询表的约束
select t.constraint_schema,t.table_name,t.constraint_name,t.constraint_type ,string_agg(c.column_name,',' order by ordinal_position) column_names
from "information_schema"."table_constraints" t inner join "information_schema"."key_column_usage" c on 1=1 
and t.table_schema ='ischema' 
and t.table_name='test_type_meta_basic' 
and t.table_schema =c.table_schema
and t.table_name=c.table_name
and t.constraint_name =c.constraint_name
group by t.constraint_schema,t.table_name,t.constraint_name,t.constraint_type;

Sqlserver

-- 查询列信息
select t.table_catalog,t.table_schema,t.table_name,t.table_type
,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,numeric_precision,numeric_scale,datetime_precision,collation_name
from "INFORMATION_SCHEMA"."TABLES" t 
inner join "INFORMATION_SCHEMA"."COLUMNS" c 
on t.table_catalog=c.table_catalog and t.table_schema=c.table_schema and t.table_name=c.table_name
where t.table_schema='ischema' and t.table_name='test_type_meta_basic';

-- 查询索引信息
select table_schema,table_name,index_name,index_type,index_type_desc,is_unique,is_primary_key
,string_agg(column_name,',' ) WITHIN GROUP ( ORDER BY key_ordinal ASC)  column_names from (
select s.name table_schema,t.name table_name,c.name column_name
,i.name index_name,i.type index_type,i.type_desc index_type_desc,i.is_unique,i.is_primary_key
,ic.key_ordinal
from sys.schemas s 
inner join "sys"."tables" t on s.schema_id =t.schema_id 
inner join "sys"."indexes" i on t.object_id =i.object_id 
inner join "sys"."index_columns" ic on i.object_id =ic.object_id and i.index_id=ic.index_id
inner join "sys"."columns" c on ic.object_id =c.object_id  and ic.column_id = c.column_id 
where t.name ='test_type_meta_basic'
) tmp group by table_schema,table_name,index_name,index_type,index_type_desc,is_unique,is_primary_key;

-- 查询表的约束
select t.constraint_schema,t.table_name,t.constraint_name,t.constraint_type ,string_agg(c.column_name,',') within group (order by ordinal_position) column_names
from "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS" t inner join "INFORMATION_SCHEMA"."KEY_COLUMN_USAGE" c on 1=1 
and t.table_schema ='ischema' 
and t.table_name='test_type_meta_basic' 
and t.table_schema =c.table_schema
and t.table_name=c.table_name
and t.constraint_name =c.constraint_name
group by t.constraint_schema,t.table_name,t.constraint_name,t.constraint_type;

Oracle

-- 查询列信息
select t.OWNER ,t.TABLE_NAME
,COLUMN_NAME,COLUMN_ID,NULLABLE,DATA_TYPE,DATA_PRECISION,DATA_SCALE,CHARACTER_SET_NAME
from "SYS"."ALL_TABLES" t 
inner join "SYS"."ALL_TAB_COLS" c 
ON t.OWNER=c.OWNER and t.TABLE_NAME=c.TABLE_NAME
where t.OWNER ='CDCUSER' and t.TABLE_NAME='TEST_TYPE_META_BASIC' ORDER BY COLUMN_ID;


-- 查询索引信息
SELECT TABLE_NAME,INDEX_NAME,LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION) agg 
FROM "ALL_IND_COLUMNS" 
WHERE TABLE_NAME='TEST_TYPE_META_BASIC' AND TABLE_OWNER ='CDCUSER'
GROUP BY TABLE_NAME,INDEX_NAME;


-- 查询表的约束
SELECT t.TABLE_NAME ,t.CONSTRAINT_NAME,t.CONSTRAINT_TYPE ,LISTAGG(c.COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) COLUMN_NAMES
FROM ALL_CONSTRAINTS t,ALL_CONS_COLUMNS c 
WHERE t.TABLE_NAME =c.TABLE_NAME 
AND t.OWNER=c.OWNER AND t.CONSTRAINT_NAME=c.CONSTRAINT_NAME 
AND t.OWNER='CDCUSER' AND t.TABLE_NAME='TEST_TYPE_META_BASIC'
GROUP BY t.TABLE_NAME ,t.CONSTRAINT_NAME,t.CONSTRAINT_TYPE;