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

电玩女神 2024-02-20 10:57 54阅读 0赞

一、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 ###

### 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;