Oracle不同权限获取表,视图,字段的sql整理方式_MsSqlserver

1. oracle获取表sql

1.1 有dba权限获取方式

select distinct table_name from dba_tables 
where owner ='库名' 
and  owner not in ('sys','system','outln','xdb','wmsys','ctxsys','mdsys')

1.2 普通权限获取方式

select distinct table_name from all_tables 
where owner ='库名' 
and  owner not in ('sys','system','outln','xdb','wmsys','ctxsys','mdsys')

2. oracle获取表字段sql

2.1 有dba权限获取方式

select upper(t.table_name),nvl(cc.comments, ''),c.column_name,cc.comments,c.data_type,c.data_length,c.data_precision,c.data_scale
 ,nullable,case when pk.constraint_type = 'p' then 'y'  else 'n'  end
 from dba_tables t join dba_tab_columns c on t.table_name = c.table_name and t.owner = c.owner
 left join dba_col_comments cc on c.owner = cc.owner and c.table_name = cc.table_name and c.column_name = cc.column_name
 left join 
    (select cons.table_name, cons.owner, cols.column_name, cons.constraint_type
     from dba_constraints cons 
     join dba_cons_columns cols on cons.constraint_name = cols.constraint_name 
     where cons.constraint_type = 'p'
    ) pk on c.owner = pk.owner and c.table_name = pk.table_name and c.column_name = pk.column_name
    where upper(t.table_name) in ( 逗号分隔的表名 ) and t.owner not in ('sys', 'system', 'outln', 'xdb', 'wmsys', 'ctxsys', 'mdsys')
 order by t.table_name, c.column_id

2.2 普通权限获取方式

select upper(t.table_name),nvl(cc.comments, ''),c.column_name,cc.comments,c.data_type,c.data_length,c.data_precision,c.data_scale
 ,nullable,case when pk.constraint_type = 'p' then 'y'  else 'n'  end
 from all_tables t join all_tab_columns c on t.table_name = c.table_name and t.owner = c.owner
 left join all_col_comments cc on c.owner = cc.owner and c.table_name = cc.table_name and c.column_name = cc.column_name
 left join 
    (select cons.table_name, cons.owner, cols.column_name, cons.constraint_type
     from all_constraints cons 
     join all_cons_columns cols on cons.constraint_name = cols.constraint_name 
     where cons.constraint_type = 'p'
    ) pk on c.owner = pk.owner and c.table_name = pk.table_name and c.column_name = pk.column_name
    where upper(t.table_name) in ( 逗号分隔的表名 ) and t.owner not in ('sys', 'system', 'outln', 'xdb', 'wmsys', 'ctxsys', 'mdsys')
 order by t.table_name, c.column_id

3. oracle获取视图sql

3.1 有dba权限获取方式

select distinct view_name from all_views 
where owner not in ('sys','system','outln','xdb','wmsys','ctxsys','mdsys') 
and owner ='库名';

3.2 普通权限获取方式

select distinct view_name from all_views 
where owner not in ('sys','system','outln','xdb','wmsys','ctxsys','mdsys') 
and owner ='库名';

4. oracle获取视图字段sql

4.1 有dba权限获取方式

select 
		c.table_name as view_name, 
		nvl ( cm.comments, '' ) as cmt, 
		c.column_name, 
		cm.comments, 
		c.data_type, 
		c.data_length, 
		c.data_precision, 
		c.data_scale, 
		c.nullable, 
		 'n' as is_primary_key 
from 
		dba_tab_columns c 
left join dba_col_comments cm on c.owner = cm.owner 
and c.table_name = cm.table_name 
and c.column_name = cm.column_name 
where 
		c.owner = '库名' 
and c.table_name in ( 逗号分隔的表名 )
order by c.table_name,c.column_id

4.2 普通权限获取方式

select 
		c.table_name as view_name, 
		nvl ( cm.comments, '' ) as cmt, 
		c.column_name, 
		cm.comments, 
		c.data_type, 
		c.data_length, 
		c.data_precision, 
		c.data_scale, 
		c.nullable, 
		 'n' as is_primary_key 
from 
		all_tab_columns c 
left join all_col_comments cm on c.owner = cm.owner 
and c.table_name = cm.table_name 
and c.column_name = cm.column_name 
where 
		c.owner = '库名' 
and c.table_name in ( 逗号分隔的表名 )
order by c.table_name,c.column_id