SQL脚本杂记

# 1 Oracle根据表名反查job和存储过程

select *
from user_dependencies
where referenced_name=upper('TAB_NET_SIGNOMITARRIVE_STAT')

1
2
3

select *
from user_jobs
where upper(what) like '%SIGNOMITARRIVE%'

1
2
3

select *
from tab_job_monitor
where upper(procedure_name) like '%SIGNOMITARRIVE%'
order by createdate desc

1
2
3
4

# 2 Oracle查询库表所有列

select
  a.column_name 列名,
  b.COMMENTS 注释,
  CASE a.nullable
      WHEN 'Y' THEN ''
      WHEN 'N' THEN '否'
      END 是否可为空
from all_tab_columns a
    left join user_col_comments b
        on a.COLUMN_NAME = b.COLUMN_NAME
        and a.TABLE_NAME = b.TABLE_NAME
where a.table_name='表名'
  AND a.owner = UPPER('用户名');

1
2
3
4
5
6
7
8
9
10
11
12
13

上次更新: 2022/06/02, 11:20:10

← Java代码杂记批量算费本地工具类→