数据库语法总结（5）——元数据查询

文章持续更新，本章内容是根据数据库策略进行的讲解，可能大家会有不理解的地方，可以留言咨询或查看一些其它的资料辅助。

1、列出模式中的所有表

也可以理解为列出用户下的表，需要我们查询系统表，每种数据库的名称的方式略有不同

DB2：

select tabname from syscat.tables where tabschema='SYSTEM';

Oracle:

select table_name from all_tables where owner='SYSTEM';

PostgreSQL、Mysql、SQLserver

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

2、列出表中的列

想要查找某表中某列的数据类型，位置。注意表名和用户名区分大小写

DB2

select colname,typename.colno  from syscat.columns 
where tabname='T_EMP' and tabschema='SYSTEM';

Oracle

select column_name,data_type,column_id from all_tab_columns
 where owner='SYSTEM' and table_name='T_EMP';

PostgreSQL、Mysql、Sqlserver

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

3、列出表的索引列

查找表的索引、索引基于的列和这些列在索引中的位置

DB2

select a.tabname,b.indname,b.colname,b.colseq 
 from syscat.indexes a,syscat.indexcoluse b
 where a.indschema=b.indschema and a.indname=b.indname
 and a.tabname='T_EMP' and a.tabschema='SYSTEM'; 

Oracle

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

PostgreSQL

select a.tablename,a.indexname,b.column_name
 from pg_catalog.pg_indexes a,information_schema.column b
 where a.tablename=b.table_name
 and a.schemaname='SYSTEM';

Mysql比较简单，在相应的用户/模式下建立SQL查询编辑器，执行show index

show index from T_EMP;

Sqlserver需要查询的表较多,也是需要找到对应的用户/模式下执行

select a.name table_name,
       b.name index_name,
       d.name column_name,
       c.index_column_id
 from sys.tables a,sys.indexes b,
      sys.index_columns c,sys.columns d
 where a.object_id=b.object_id 
   and b.object_id=c.object_id and b.index_id=c.index_id
   and c.object_id=d.object_id and c.column_id=d.column_id
   and a.name='T_EMP'; 

4、列出表的约束

查找表的约束，以及约束是在哪些列上定义的。(在关系数据库中，约束很重要，我们可以通过查找的方式进行检查约束)

DB2

select a.tabname,a.constname,b.colname,a.type
 from syscat.tabconst a,syscat.columns b
 where a.tabname=b.tabname and a.tabschema=b.tabschema
   and a.tabname='T_EMP' and a.tabschema='SYSTEM';

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=b.table_name and a.owner=b.owner
   and a.constraint_name=b.constraint_name
   and a.table_name='T_EMP' and a.owner='SYSTEM';

PostgreSQL、Mysql、Sqlserver

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=b.table_name
   and a.table_schema=b.table_schema
   and a.constraint_name=b.constraint_name
   and a.table_name='T_EMP' and a.table_schema='SYSTEM';

5、列出没有相应索引的外键

查看表的外键上是否创建了索引（直接在父子表中子表列字段上创立索引可能会造成锁表，对此在外键上创建索引更有利于优化性能）

DB2

select fkeys.tabname,
       fkeys.constname,
       fkeys.colname,
       ind_cols.indname
 from (
    select a.tabschame,a.tabname,a.constname,b.colname
    from syscat.tabconst a,syscat.keycoluse b
    where a.tabname=b.tabname and a.tabschema=b.tabschema
         and a.tabname='T_EMP' 
         and a.tabschema='SYSTEM' 
         and a.type='F'
    )fkeys
    left join
    ( 
    select a.tabschema,
       a.tabname,
       a.indname,
       b.colname
    from syscat.indexes a,syscat.indexcoluse b
    where a.indschema=b.indschema and a.indname=b.indname
    )ind_cols
    on (fkeys.tabschema =ind_cols.tabschema
    and fkeys.tabname=ind_cols.tabname
    and fkeys.colname=ind_cols.colname )
where ind_cols.indname is null;
     

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.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
  and a.table_name='T_EMP'
  and a.owner='SYSTEM'
  and b.constraint_type='R';

关于constraint_type约束类型扩展

Type Code	Type Description	Acts On Level
C	Check on a table  Column	Column
O	Read Only on a view	Object
P	Primary Key  （主键）	Object
R	Referential AKA Foreign Key（外键）	Column
U	Unique Key （唯一）	Column
V	Check Option on a view	Object

PostgreSQL

select  fkeys.table_name,
        fkeys.constraint_name,
        fkeys.column_name,
        ind_cols.indexname
 from (
    select a.constraint_schema,
        a.table_name,
        a.constraint_name,
        a.column_name
    from infromation_schema.key_column_usage a,
         information_schema.referential_constraint b
 where a.constraint_name=b.constraint_name
   and a.constraint_schema=b.constraint_schema
   and a.constraint_name='SYSTEM'
   and a.table_name='T_EMP'
    )fkeys 
    left join 
    (
    select a.shemaname.a.tablename,a.indexname,b.column_name
    from pg_catalog.pg_indexes a,information_schame b
    where a.tablename=b.table_name
      and a.schamename=b.table_schame
    )ind_cols
    on
    ( fkeys.constraint_schema=ind_cols.schemaname
    and fkeys.table_name=ind_cols.tablename
    and fkeys.column_name=ind_cols.column_name )
where ind_cols.indexname is null;

Sqlserver

select fkeys.table_name,
    fkeys.constraint_name,
    fkeys.column_name,
    ind_cols.index_name
from 
    (
    select a.object_id,
        d.column_id,
        a.name table_name,
        b.name constraint_name,
        d.name column_name
    from sys.tables a 
    join sys.foreign_keys b 
    on (a.object_id=b.parent_object_id and a.name='T_EMP')
    join sys.foreign_key_columns c
    on(b.object_id=c.constraint_object_id)
    join sys.columns d
    on(c.constraint_column_id=d.column_id and a.object_id=d.object_id)
    )fkeys
left join
    (
    select a.name index_name,b.object_id,c.column_id
    from sys.indexes a,sys.index_columns b
    where a.index_id=b.index_id
    )ind_cols
on(fkeys.object_id=ind_cols.object_id
and fkeys.column_id=ind_clos.column_id)
where ind_cols.index_name is null;
 

Mysql

（1）可以使用show index来检索索引信息，比如索引的名称、索引包含的列，以及这些列在索引中的序数位置；

（2）或者查询表information_shema.key_column_usage列出表给定的外键，然后执行show index，将二者结果进行比较，如果前者有，后者没有，就确定该列没有建索引。

6、使用SQL生成SQL

想要创建动态SQL语句。达到数据自动执行维护的任务，我们以实现下面3点为例：

计算表中的行数；禁用给表定义的外键约束；根据表中的数据生成插入脚本。

我们可以通过使用字符串来创建SQL语句，但是注意，此处只是生成语句，我们要执行还必须手动在脚本中执行才能达到效果。我们此处以Oracle为例子，其他的数据库也同样适用，只是字典名称，表名，日期格式等略有不同。

生成计算所有表中行数的SQL

SELECT 'SELECT COUNT(*) from '||table_name||';' cnts from user_tables;

禁用所有表中定义的外键约束

select ' alter table '||table_name||' 
 disable constraint '||constraint_name||' ;' cons
from user_constraints where constraint_type='R';

根据T_EMP表的一些列生成插入脚本

select 'insert into T_EMP(EMPID,EMPNAME,EMPDATE) '||chr(10)||' 
 values ( '||EMPID||','||''''||EMPNAME ||''',
 to_date('||''''||EMPDATE||''') );' inters 
from T_EMP where EMPID=1;

扩展：chr()将ASCII码转换为字符，常见的有

chr(9) --制表符
chr(10) --换行符
chr(13) --回车符
chr(32) --空格符
chr(34) --双引号“"” （单面双引号）

7、 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';

8、总结

最后，由于上述提到的表太多了，做个简单总结方便大家记忆吧

数据库类型	系统表	描述
DB2	syscat.tables	数据库中对象的信息
	syscat.views	视图的定义信息
	syscat.columns	表或视图每一列的信息
	syscat.indexes	每个表或视图对应的索引
	syscat.indexcoluse	所有列的位置
	syscat.tabconst	为每个唯一（U）、主键（P）、外键（F）或 表检查（K）的约束
	syscat.keycoluse	查看表主键
oracle	all_tables	用户定义的所有表的信息
	all_tab_columns	所有表或视图中所有列的信息
	all_tab_comments	所有注释信息
	all_ind_columns	索引列的信息
	all_constraints	所有约束的信息
	all_cons_columns	查询表中约束列的相关信息
	user_tables	当前用户拥有的表
	user_constraints	当前用户的约束信息
	dictionary	所有数据字典视图及其用途
	dict_columns	数据字典视图包含的列的描述，字段类型，大小等
postgresql masql sqlserver	information_schema.tables	所有表的信息
	information_schema.columns	所有列的信息
	information_schema.table_constraints	表的约束信息
	information_schema.key_column_usage	查看索引列上的约束
postgresql	pg_catalog.pg_indexes	索引的信息
	information_schema. referential_constraint	提供外键的信息
	information_schame	提供对数据库元数据的访问
mysql	show index	表的索引展示
sqlserver	sys.tables	表信息
	sys.indexes	索引信息
	sys.index_columns	索引列位置信息
	sys.columns	列的信息
	sys.foreign_keys	外键约束信息
	sys.foreign_key_columns	外键与列的相关信息

 在此也区分说一下约束和索引：

1、约束和索引， 前者是用来检查数据的正确性，后者用来实现数据查询的优化，目的不同。 

2、索引可以转变成约束，但是反过来不可以

3、删除时应该先删除约束，然后再删除索引

4、主键是约束，不是索引

好了，今天的介绍就到这里了，有问题会持续更新，有补充的可以给我留言，请多关注哦！