oracle 语句 查询所有表名称、查询无主键表名称、查询大文本的数据表、sqlserver查oracle,查询视图对应得表名

查询所有表名称
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='DONG' order by TABLE_NAME

select t.table_name tableName, f.comments comments
  from user_tables t
 inner join user_tab_comments f
    on t.table_name = f.table_name
   

查询视图对应得表名

create table aaa as select * from user_views

select view_name,to_char(substr(text,instr(text,'from  JHICU.') ,200))from aaa

 

查询无主键表名称

    
    select table_name
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name)

 

查询主键

select
con.table_name,to_char( wm_concat( col.column_name))
from
 user_constraints con,user_cons_columns col
where
 con.constraint_name=col.constraint_name and con.constraint_type='P'
 --and col.table_name='ADT_LOG'
 group by con.table_name

 

查询大文本的数据表

       select t.column_name ,DATA_TYPE,TABLE_NAME
from user_tab_columns t
where 
 DATA_TYPE='CLOB'

 

sqlserver查oracle

SELECT * FROM OPENQUERY(ORA_LINK,'SELECT * FROM TABLE');