查找 SQL 中引用的列名和表名 [英] find column names and table names referenced in SQL

问题描述

如何查找 SQL 中使用的所有表名和列名?它位于 ORACLE 数据库上.下面是一个 SQL 示例.

How to find all the tables and column names used in a SQL? It is on ORACLE database. Below is an SQL example.

SELECT 
   A.ENAME,
   A.AGE as EMP_AGE,
   B.DNAME 
FROM
   emp a,
   dept b
WHERE
   a.deptno= b.deptno

我希望输出是这样的

TABLENAME, COLUMNNAME
EMP, ENAME
EMP, DEPTNO
EMP, AGE
DEPT, DNAME
DEPT, DEPTNO

我做了一些研究，但未能找到完美的解决方案.如果我们创建视图或存储过程有帮助吗?请指教.

I did some research and failed to find a perfect solution. does it help if we create a view or stored procedure? Please advise.

推荐答案

我有一个很好的解决方案，但你需要做两件事:

I have a great solution for you, but there are two things you will need to do:

1. 将 SQL 放在 PL/SQL 程序单元中.所以，是的，你提到的存储过程.

1. Place the SQL inside a PL/SQL program unit. So, yes, to the stored procedure you mentioned.

在 12.2 实例上编译该程序单元和所有相关表(即安装您的应用程序代码)(您可以在 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 或者您可以购买访问 cloud.oracle.com 的 Exadata Express CLoud 服务，或访问 cloud.oracle.com/tryit 获得 300 美元的信用额度，免费使用一个月).

Compile that program unit and all dependent tables (that is, install your application code) on a 12.2 instance (you can download 12.2 at http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html or you can purchase an Exadata Express CLoud Service at cloud.oracle.com or get a $300 credit to use one at no cost for a month at cloud.oracle.com/tryit).

12.2 是关键，因为您真正想要使用的功能称为 PL/Scope，它是一个编译器工具，用于收集有关 PL/SQL 标识符(从 11.1 开始)和 PL/'SQL(从 12.2 开始)中的 SQL 使用情况的信息).

12.2 is key because the feature you REALLY want to use is called PL/Scope and it is a compiler tool that collections information about PL/SQL identifiers (as of 11.1) and SQL usage inside PL/'SQL (as of 12.2).

CREATE TABLE my_data (n NUMBER)
/

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'
/

CREATE OR REPLACE PROCEDURE my_procedure (n_in IN NUMBER)
   AUTHID DEFINER
IS
   l_n           my_data.n%TYPE;

   CURSOR all_data_cur
   IS
          SELECT *
            FROM my_data
      FOR UPDATE OF n;
BEGIN
   INSERT INTO my_data (n)
        VALUES (n_in);

END;
/

  SELECT idt.line,
         idt.owner || '.' || idt.object_name code_unit, 
         idt.name column_name,
         RTRIM (src.text, CHR (10)) text
    FROM all_identifiers idt, all_source src
   WHERE     idt.usage = 'REFERENCE'
         AND idt.TYPE = 'COLUMN'
         AND idt.line = src.line
         AND idt.object_name = src.name
         AND idt.owner = src.owner
         AND idt.object_name = 'MY_PROCEDURE'
ORDER BY code_unit, line
/

LINE CODE_UNIT          COLUMN_NAME TEXT  
4   STEVEN.MY_PROCEDURE N           l_n           my_data.n%TYPE;
10  STEVEN.MY_PROCEDURE N           FOR UPDATE OF n;
12  STEVEN.MY_PROCEDURE N           INSERT INTO my_data (n)

希望有帮助！

在 livesql.oracle.com 上有更多 PL/Scope 示例.只需搜索pl/scope"(废话).

Lots more examples of PL/Scope at livesql.oracle.com. Just search for "pl/scope" (duh).

这篇关于查找 SQL 中引用的列名和表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！