SQL经典实例（五）元数据查询

列举模式中的表

Oracle

select table_name 
    from all_tables where owner = 'SCOTT';

MySQL

select table_name
    from information_schema.tables
  where table_schema = 'mysql';

列举字段

Oracle

select column_name, data_type, column_id
    from all_tab_columns
where owner = 'SCOTT' and table_name = 'EMP';

MySQL

select column_name, data_type, ordinal_position
    from information_schema.columns
where table_schema = 'mysql'
    and table_name = 'emp';

列举索引列

Oracle

select table_name, index_name, column_name, column_position
    from sys.all_ind_columns 
  where table_name = 'EMP'
    and table_owner = 'SCOTT';

MySQL

show index from emp;

列举约束

Oracle

select a.table_name,
       a.constraint_name,
       b.column_name,
       a.constraint_type
  from all_constraints a,
       all_cons_columns b
 where a.table_name = 'EMP'
   and a.owner = 'SCOTT'
   and a.table_name = b.table_name
   and a.owner = b.owner
   and a.constraint_name = b.constraint_name;

MySQL

select a.table_name,
       a.constraint_name,
       b.column_name,
       a.constraint_type
  from information_schema.table_constraints a,
       information_schema.key_column_usage b
 where a.table_name = 'emp'
   and a.table_schema = 'mysql'
   and a.table_name = b.table_name
   and a.table_schema = b.table_schema
   and a.constraint_name = b.constraint_name;

列举非索引外键

列出含有非索引外键的表。例如，希望确认emp表的外键是否加入了索引：
Oracle

select a.table_name,
       a.constraint_name,
       a.column_name,
       c.index_name
  from all_cons_columns a,
       all_constraints b,
       all_ind_columns c
 where a.table_name = 'EMP'
   and a.owner = 'SCOTT'
   and b.constraint_type = 'R'
   and a.owner = b.owner
   and a.table_name = b.table_name
   and a.constraint_name = b.constraint_name
   and a.owner = c.table_owner(+)
   and a.table_name = c.table_name (+)
   and a.column_name = c.column_name (+)
   and c.index_name is null;

描述Oracle数据字典视图

查询dictionary视图，并列出数据字典视图及其目的：

select table_name, comments
    from dictionary
order by table_name;

查询dict_columns并找出某个数据字典视图的各列含义：

select column_name, comments
    from dict_columns
where table_name = 'ALL_TAB_COLUMNS';

《SQL经典实例》第五章