查找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:
-
将SQL放在PL/SQL程序单元中.所以,是的,对于您提到的存储过程.
Place the SQL inside a PL/SQL program unit. So, yes, to the stored procedure you mentioned.
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内部的SQL用法(自12.2起)的信息. ).
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"(duh).
Lots more examples of PL/Scope at livesql.oracle.com. Just search for "pl/scope" (duh).
这篇关于查找SQL中引用的列名和表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!