虚谷数据库-如何通过系统表查询表的相关信息

1、查询库中某个表是否存在

--非系统库下查询，使用拥有dba权限的用户查询，表名需要大写
select count(*) from dba_tables where table_name ='TABLE_NAME';
--在非系统库,使用拥有dba权限的用户查询，用小写表名查询的方法
select count(*) from dba_tables where lower(table_name)='table_name';
--在系统库（SYSTEM)下，表名可以大写也可以小写
select count(*) from sys_tables where table_name ='TABLE_NAME/table_name';

2、查询某个模式里的所有表

--在任何库下使用拥有dba权限的用户查询
select db_name,schema_name,table_name from dba_tables  t
inner join dba_schemas s on t.schema_id=s.schema_id and t.db_id=s.db_id
inner join dba_databases d on t.db_id=d.db_id
where schema_name='模式名'
and db_id='库id';

3、查询表的关联关系

--在任何库下使用任何用户查询
select ut1.table_name as fk_name,ut2.table_name as pk_name
from
user_constraints uc
join
user_tables ut1
on
uc.table_id=ut1.table_id
join
user_tables ut2
on
uc.ref_table_id=ut2.table_id;

4、表结构查询（登陆指定库）

--SQL1:（在任何库下使用拥有dba权限的用户查询）
select t.table_name,t.table_id,c.col_no,c.col_name,c.type_name,trunc(c.scale/65536)::int as scale_1,mod(c.scale,65536) as scale_2,c.not_null,c.comments from dba_columns c,dba_tables t where t.table_id=c.table_id and c.db_id=current_db_id and t.table_name='表名' order by c.col_no;

--SQL2:（用户SYSDBA登陆系统库SYSTEM执行，比SQL1多了指定模式，查询出的字段与SQL1相同）
select t.table_name,t.table_id,c.col_no,c.col_name,c.type_name,trunc(c.scale/65536)::int as scale_1,mod(c.scale,65536) as scale_2,c.not_null,c.comments from sys_columns c join sys_tables t on t.table_id=c.table_id  and t.db_id=c.db_id join sys_schemas s on t.schema_id=s.schema_id and t.db_id=s.db_id join sys_databases d on t.db_id=d.db_id where D.db_name='库名' and t.table_name='表名' and s.schema_name='模式名' order by c.col_no;

--SQL3:(--在任何库下使用拥有dba权限的用户查询，比SQL1多个字段类型是否是可变长度的字符串 varying)
select t.table_name,t.table_id,c.col_no,c.col_name,c."varying",decode(c."varying",true,'VARCHAR',c.type_name) t_name,
trunc(c.scale/65536)::int as scale_1,mod(c.scale,65536) as scale_2,c.not_null,c.comments from dba_columns c,dba_tables t 
where t.table_id=c.table_id and c.db_id=current_db_id and t.table_name='表名' order by c.col_no;


--SQL4:(在任何库下使用拥有dba权限的用户查询，适合于分区表的表结果查询)
select schema_name,col_name,parti_type,parti_num,parti_key,* from dba_columns c
inner join dba_tables t  on t.table_id=c.table_id
inner join dba_schemas s on t.schema_id=s.schema_id
where schema_name='模式名'
and table_name='表名'
order by col_no;

5、单张表数据大小统计(TB、GB)

--SQL1:(--用户SYSDBA登陆系统库SYSTEM执行，单位TB)
select d.db_name,sn.schema_name,t.table_name,count(*)*8/1024/1024||'T'  as cnt from sys_schemas sn,sys_tables t,sys_gstores s,sys_databases d where s.obj_id=t.table_id and sn.schema_id=t.schema_id and sn.db_id=t.db_id and s.db_id=d.db_id and sn.db_id=t.db_id and db_name='库名' and t.table_name='表名' group by t.table_name,sn.schema_name,d.db_name;  

--SQL2:(--用户SYSDBA登陆系统库SYSTEM执行，单位GB) 
select d.db_name,sn.schema_name,t.table_name,count(*)*8/1024 || 'G' as cnt  from sys_schemas sn,sys_tables t,sys_gstores s,sys_databases d where s.obj_id=t.table_id and sn.schema_id=t.schema_id and sn.db_id=t.db_id and s.db_id=d.db_id and sn.db_id=t.db_id and db_name='库名' and t.table_name='表名' group by t.table_name,sn.schema_name,d.db_name;        

6、查询某个列在哪些表中存在

--在任何库下使用拥有dba权限的用户查询
select s.schema_name,t.table_name from dba_tables t,dba_columns c,dba_schemas s where t.schema_id=s.schema_id 
and t.table_id=c.table_id and c.col_name='列名';

7、查看表分区名

--在任何库下使用拥有dba权限的用户查询
select parti_name,parti_val from dba_partis p,dba_tables t,dba_schemas s where p.table_id=t.table_id and t.schema_id=s.schema_id and t.table_name='表名' and s.schema_name='模式名' order by parti_val;

8、查询表分区情况

--在任何库下使用拥有dba权限的用户查询
select t.table_name,t.parti_key,T.AUTO_PARTI_SPAN,DECODE(T.AUTO_PARTI_TYPE,1,'年',2,'月',3,'日') from dba_tables t,dba_schemas s where t.schema_id=s.schema_id and s.schema_name='模式名' and regexp_like(t.table_name,'abc');--以abc开头的table_name

9、查询列，列顺序

--在任何库下使用拥有dba权限的用户查询
declare
sql varchar;
a int;
na varchar;
begin
na:='AA';
sql:='select ';
a:=0;
for i in (select a.col_name from DBA_columns a,DBA_tables b,DBA_SCHEMAS c where c.schema_id=b.schema_id and a.table_id=b.table_id 
and b.table_name=na and c.schema_name='模式名' order by col_no) loop
if a=0 then 
sql:=sql||i.col_name;
else
sql:=sql||','||i.col_name;
end if;
a :=a+1;
end loop;
sql:=sql||' from usr_sod.'||na;
send_msg(sql);
end;
/

10、对比模式下的表名

--在任何库下使用拥有dba权限的用户查询
select a.table_name from (select t.table_name from dba_tables t join dba_schemas s on t.schema_id=s.schema_id and s.schema_name='TCZX_TY_AOBOIP') a,
(select t.table_name from dba_tables t join dba_schemas s on t.schema_id=s.schema_id and s.schema_name='TCZX_TY_IOOIP') b,(select t.table_name from dba_tables t join dba_schemas s on t.schema_id=s.schema_id and s.schema_name='TCZX_TY_EPLUS') c 
where a.table_name=b.table_name and b.table_name=c.table_name;

11、查询指定模式下的分区表

--在任何库下使用拥有dba权限的用户查询
select s.schema_name,t.table_name
from dba_partis p
join dba_tables t on p.table_id=t.table_id
join dba_schemas s on t.schema_id=s.schema_id 
where s.schema_name in ('A','B','C','D')
group by s.schema_name,t.table_name
order by s.schema_name,t.table_name;

12、查看没有唯一值索引的表

--在任何库下使用拥有dba权限的用户查询
select s1.schema_name,t1.table_name 
from dba_tables t1,dba_schemas s1 
where t1.schema_id=s1.schema_id 
and s1.schema_name='模式名'
and table_name not in (select t.table_name from dba_tables t,dba_indexes i,dba_schemas s where t.table_id=i.table_id 
and t.schema_id=s.schema_id 
and is_unique='true' 
and s.schema_name='模式名' group by t.table_name);