Selecting column names and table names of a select statement

Question

How can I select the column name and table name from a SQL?

I tried something like this but it didn't work:

select column_name, table_name from (select * from users);

This might sound silly, but I have a list of different SQLs and I need to extract their columns and tables into a list. So some of the statements could me:

select username, password from users
select createdate from userlog
select * from dept
...

If I can select the column name and table name of a select statement, then I should get, say for the first statement, username and password for columns and users for table name. And createdate for column and userlog for table name in the second statement.

Then if it all works, I can then loop through the list of select statements and extract their column and table names.

Answer 1

You need to parse the SQL statement so the SQL engine figures out the columns and datatypes of the columns that the statement returns.

How you do it best depends on what environment you are using. In some programming languages when you create a SqlPreparedStatement or OraCommand or whatever the object may be called, that object may have a metadata collection populated with column information after parsing.

If you are doing it in the database itself, parsing your statement with DBMS_SQL can get you the information you need. See Example 8 in the documentation at this link:

http://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68205

--

Oh, and this gives you column names of the select statement. The table names I do not know of any way to get easily.

Answer 2

The below query worked for Oracle database.

SELECT COLUMN_NAME,TABLE_NAME FROM ALL_TAB_COLUMNS 

You can see more about information-schema

Edit:

You may try like this:

SELECT COLUMN_NAME,TABLE_NAME FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME IN (SELECT ColumnName FROM users)