数据库的一些字典表查询和分页查询…

总结一下最近开发用到的一些数据库查询,以便日后查阅.

 

Oracle(8I/9I/10G/11G)

一,查询库中所有表(视图)名和对应的用户名

8I:(因为8i不支持left/right)

SELECT CONCAT(O.OWNER,O.OBJECT_NAME) AS OWNERANDOBJECTNAME ,O.OWNER AS OWNER,O.OBJECT_NAME AS OBJECTNAME,O.OBJECT_TYPE AS OBJECTTYPE,C.COMMENTS AS COMMENTS FROM ALL_OBJECTS O,ALL_TAB_COMMENTS C WHERE O.OBJECT_NAME = C.TABLE_NAME(+) AND O.OWNER = C.OWNER(+) AND O.OBJECT_TYPE IN ('TABLE','VIEW') order by O.OWNER

 

9I/10G/11G:

SELECT CONCAT(O.OWNER,O.OBJECT_NAME) AS OWNERANDOBJECTNAME ,O.OWNER AS OWNER,O.OBJECT_NAME AS OBJECTNAME,O.OBJECT_TYPE AS OBJECTTYPE,C.COMMENTS AS COMMENTS FROM ALL_OBJECTS O LEFT JOIN ALL_TAB_COMMENTS C ON O.OBJECT_NAME = C.TABLE_NAME AND O.OWNER = C.OWNER WHERE O.OBJECT_TYPE IN ('TABLE','VIEW') order by O.OWNER

 

二,查询表字段信息,包括是否是主键索引

8I/9I/10G/11G:

select atc.column_name,
       atc.data_type,
       NVL(atc.DATA_PRECISION, atc.data_length) AS data_length,
       acc.comments,
       (select 'Y'
          from all_cons_columns accs, all_constraints ac
         where accs.owner = atc.owner
           and ac.owner = accs.owner
           and accs.TABLE_NAME = atc.table_name
           and ac.CONSTRAINT_TYPE = 'P'
           and accs.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
           and accs.column_name = atc.column_name) AS dbKeyFlg,