Application that I can paste a query into & extract column/table names

[Roobmeister]

Hello,

I have hundreds of queries that I need metadata for.

I am looking for an application or tool that I can use that will put the following information from an Oracle SQL SELECT statement into a .csv or text file:

Expressions (ie TO_CHAR(fieldname))
Column Names
Table Names

This is the only metadata I need, so I won't need an ODBC connection to the database - all of this information is within the SQL query itself.

I am hoping that such an application or tool exists, because it would save us many hours of tedious work!

Thanks in advance..

[debasisdas]

From your post i am not sure what exactly you are looking for .

[Roobmeister]

Here's an example: Let's say you have a query like this:

SELECT sysdate as CURRENT_DATE, father_name as FATHER, mother_name as MOTHER from CHILDREN_INFO

You would copy and paste this query into the tool, and it would return the following:

EXPRESSION COL_NAME TBL_NAME

sysdate CURRENT_DATE CHILD_INFO
father_name FATHER CHILD_INFO
mother_name MOTHER CHILD_INFO

I am looking for a tool that would do this!

I would be happy to answer any more questions...

Thanks!

[debasisdas]

i don't think there is any tool specifically for this kind of requirmnet.

[Roobmeister]

Anyone else have any ideas about how I can go about doing this? Thanks!

[amitpatel66]

You can do so by using a SPOOL command.Check
SQLPLUS spool to CSV file - dBforums
Export SQL data to a .csv file using Sqlplus - Matrise
on how to spool data to csv file

[Roobmeister]

Thank you, amitpatel66, but I do not need to export the result set to a .csv, I only want the expressions/column names/table names (see example I posted above)

[amitpatel66]

Oh ok you are looking at metadata export. Well I am afraid to say that there is no seperate tool for such requirement. But you can get the metadata by desc a table or incase if you are using TOAD then you can type the table name and press F4 will give you the metadata of that table.
You can also use dbms_metadata oracle package to get the metadata information.

[madankarmukta]

Hi,

I agree with what Amit has suggested.

There is another workaround for this if you are working with 10g.But the solution is little bit tricky.

1)Create the function which will accept the string input and return any of the composit dataType - the one you prefer in order to hold output

EXPRESSION COL_NAME TBL_NAME

sysdate CURRENT_DATE CHILD_INFO
father_name FATHER CHILD_INFO
mother_name MOTHER CHILD_

2)Make use of Regular Expression to catch the column names and vales from the string input.

3) Hold the result in variable - declared in step1

Thanks!